Dynamic Chart Titles In Power BI

As you probably know, charts (and lots of other visualisations) in Power BI have titles that can be set to any piece of static text. You can do this by selecting the chart, going to the Format tab in the Visualizations pane, then changing the properties in the Title section as shown below (full documentation here):

But what about if you want the chart title to change depending on what is selected? For example, you might be using slicers or filters to allow a user to choose which days of the week they want to see data for. In that situation you might want to add a title that shows which days of the week have actually been selected; this would be particularly important if the report uses filters, or if the report is going to be printed. Unfortunately the built in Title Text property can’t be used to display dynamic values but in this blog post I’ll show you how to solve this problem using DAX.

Here’s a simple example of a report that contains a dynamic chart title:

Using data from the Adventure Works DW database I’ve created a simple data model containing a Date dimension table called DimDate and a fact table called FactInternetSales; the DimDate table contains a field called EnglishDayNameOfWeek that contains the names of the days of the week, and the report contains a column chart that shows a Sales measure broken down by day of week. There’s also a slicer where the user can select one or more day and at the top there’s a title that lists the day names selected in the slicer and displayed in the chart.

There are two parts to the solution. The first part is to create a measure that will return the text needed for the chart title, and this relies on the DAX ConcatenateX() function that I blogged about here. Here’s the DAX for the measure:

1

2

3

4

5

6

7


Title =

"Sales Amount for "

    & CONCATENATEX (

        VALUES ( 'DimDate'[EnglishDayNameOfWeek] ),

        'DimDate'[EnglishDayNameOfWeek],

        ", "

    )

Here, the Values() function is used to return a table containing all of the selected days of the week, and this is then passed to ConcatenateX() to get a text value containing a comma delimited list of the day names.

The second part of the solution deals with how to display the value returned by the measure. In the report above I used a Card visualisation, dropped the measure above into the Field area and then turned off the Category Label on the Format tab so that only the value returned by the measure, and not the name of the measure itself, is displayed:

And this is all you need to do to recreate the report above.

We can make this better though! Instead of a simple comma delimited list of day names it would be better if we could change the last comma in the list to an “and”:

Also, if all the day names were displayed, it would be good not to display a long list of day names but show some default text instead:

Here’s the DAX for a measure that does all this:

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28


Title2 =

VAR SelectedDays =

    VALUES ( 'DimDate'[EnglishDayNameOfWeek] )

VAR NumberOfSelectedDays =

    COUNTROWS ( SelectedDays )

VAR NumberOfPossibleDays =

    COUNTROWS ( ALL ( 'DimDate'[EnglishDayNameOfWeek] ) )

VAR AllButLastSelectedDay =

    TOPN ( NumberOfSelectedDays - 1, SelectedDays )

VAR LastSelectedDay =

    EXCEPT ( SelectedDays, AllButLastSelectedDay )

RETURN

    "Sales Amount "

        & IF (

            NumberOfSelectedDays = NumberOfPossibleDays,

            "By Day Of Week",

            "For "

                & IF (

                    NumberOfSelectedDays = 1,

                    "",

                    CONCATENATEX ( 

                       AllButLastSelectedDay, 

                       'DimDate'[EnglishDayNameOfWeek], 

                       ", " )

                        & " And "

                )

                & LastSelectedDay

        )

Using a series of DAX variables to make the code more readable, here’s what this measure does:

• If the number of days selected is the same as the total number of possible days, return the title text “By Day Of Week”, otherwise
• If two or more days have been selected, then return a comma delimited list containing all but the last selected day (I used TopN() to get that table of all but the last selected day) plus a trailing “ And “. If only one day has been selected, return an empty string. Then
• Concatenate the last selected day to the text returned by the previous step. I’ve used the Except() function to find the day that was excluded by the TOPN() function in the previous step.

You can download a .pbix file containing all the code from this post here and I’ve published the report here.