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):

image

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:

image

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:

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:

image

image

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”:

image

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:

image

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

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.

18 thoughts on “Dynamic Chart Titles In Power BI

  1. Wonderful. I have been thinking for this for a while on how to write some dynamic text on dashboard based on user selections. This definitely showed me a way to do it and I am now able to derive dynamic insights on reports in text form.

  2. The only downside of techniques like this is that, when you go to pin the chart to a dashboard, the title doesn’t get pinned. I don’t think that’s a deal-breaker, but thought I’d mention it.

  3. 1. Very, very helpful. Thanks!!! 2. I´m looking for a way to change the colour/ show the data only for the selected item(s). In your example: If “Monday” and “Friday” are selected, then a) the column colour of the other days remains green; b) the names of the other days are faded out; c) the column colour of “Monday” and “Friday” turns red and d) the “Sales Amount” is shown (over the column) only for “Monday” and “Friday”. These steps are easy to implement in Excel, but so far I failed in PBID. Do you think this problem could be also solved with the approach you described for “Title2”?

    • Hmm, I don’t think the solution would be much like the one here. To change the colour you’d probably need to use several measures and maybe a stacked bar chart; it’s something I’d need to think about, and maybe a good topic for another blog post!

  4. Hi Chris excellent post !!!!
    Did you know a technique to pre select the CurrentDay or CurrentYear in a slicer
    Thanks !

  5. Thanks for sharing this post. It was really helpful for me that how to displaying the filter values as title dynamically. I was trying to implement your approach according to my requirement.

    I am facing some issues while displaying the title. Actually I have been used more than 5 slicers(5 columns from 2 tables) as filters for my PBID. I restricted to select slicers as single selection. No multiple selection allowed. Now my requirement is I need to show my title according to select my slicer values. But Its showing all values in title if I haven’t selected any value from slicers. If I select particular value from slicer then it shows those values only in title. I want to show my title as general if I don’t select any values from any of the slicers instead of showing all values of all slicers. It looks messy.

    Could you please guide me how to display values only which I selected from all slicers? I am new to power bi.

    I am using below DAX formula for displaying title. It shows all slicer values of (VKORG,REGION,NAME) while showing initially but I want to display generally if I don’t apply any filter in slicer.

    Note: All above columns data are not having unique values in tables.

    DAX Formula
    =============
    Title =
    “CRM & KTOC Data Report for ”
    & CONCATENATEX (
    VALUES ( ‘KTOC DATA'[VKORG]);’KTOC DATA'[VKORG];” , “)
    & ” , ” & CONCATENATEX (
    VALUES ( ‘CRM DATA'[Opportunity Owner: Region]);’CRM DATA'[Opportunity Owner: Region];” , “)
    & ” , ” & CONCATENATEX (
    VALUES ( ‘CRM DATA'[Opportunity Owner: Full Name]);’CRM DATA'[Opportunity Owner: Full Name];” , “)

    Thanks in advance.

    Regards
    Sridevi

  6. Hi,

    Excellent article. It was very useful for me. I am new to power bi and this article really helped me. I was trying to implement this feature to my PBID according to my requirement. But I am facing some issues.

    Could you please guide me how to implement this functionality according to my requirement.

    Actually I am using 5 different columns in slicers(5) for filters from 2 different tables. Data of columns are not having unique values. I want to display my title as generally if I don’t select none of slicers or don’t apply any filters in my slicers. But if I apply filter in my slicers then my title would be displayed dynamically according to my filter selection in slicer.

    I used below DAX formula.

    Title =
    “CRM & KTOC Data Report for ”
    & CONCATENATEX (
    VALUES ( ‘KTOC DATA'[VKORG]);’KTOC DATA'[VKORG];” , “)
    & ” , ” & CONCATENATEX (
    VALUES ( ‘CRM DATA'[Opportunity Owner: Region]);’CRM DATA'[Opportunity Owner: Region];” , “)
    & ” , ” & CONCATENATEX (
    VALUES ( ‘CRM DATA'[Opportunity Owner: Full Name]);’CRM DATA'[Opportunity Owner: Full Name];” , “)

    As of now I try to display the 3 slicers data in title. Example: I used VKORG,Region,Name as slicers and try to show those slicers values based on the selection in title dynamically. By default I want to display my title as general format like “CRM & KTOC Data Report for all”. If I select value VKORG slicer then my title would be displayed as “CRM & KTOC Data Report for Italy”.

    Note: I restricted my slicer selection as single selection instead of multiple based on my requirement. No multiple selections allowed here

    Could you please help me how to resolve this issue. Thanks in advance.

    Regards
    Sridevi

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s