DAX · Power BI · Power BI Desktop

Dynamic Chart Titles In Power BI

UPDATE April 2019: It is now possible to use DAX expressions such as the ones described in this post directly in the Title property of a visual. See https://powerbi.microsoft.com/en-us/blog/power-bi-desktop-april-2019-feature-summary/#dynamicTitles

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:

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:

[sourcecode language=”text” padlinenumbers=”true”]
Title =
"Sales Amount for "
& CONCATENATEX (
VALUES ( ‘DimDate'[EnglishDayNameOfWeek] ),
‘DimDate'[EnglishDayNameOfWeek],
", "
)
[/sourcecode]

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:

[sourcecode language=”text”]
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
)
[/sourcecode]

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.

60 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”?

    1. Chris Webb – My name is Chris Webb, and I work on the Fabric CAT team at Microsoft. I blog about Power BI, Power Query, SQL Server Analysis Services, Azure Analysis Services and Excel.
      Chris Webb says:

      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!

    1. Chris Webb – My name is Chris Webb, and I work on the Fabric CAT team at Microsoft. I blog about Power BI, Power Query, SQL Server Analysis Services, Azure Analysis Services and Excel.
      Chris Webb says:

      The best thing to do is create a calculated column in your Date table that flags the current day or the days in the current year, something like what I show in this post:
      https://blog.crossjoin.co.uk/2013/01/24/building-relative-date-reports-in-powerpivot/

      That way you can just select the ‘Current Day’ value in that column, or whatever, and it will always show the current day – assuming that your Date table is refreshed every day

  4. 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

  5. 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

    1. Chris Webb – My name is Chris Webb, and I work on the Fabric CAT team at Microsoft. I blog about Power BI, Power Query, SQL Server Analysis Services, Azure Analysis Services and Excel.
      Chris Webb says:

      Hmm, good question – it seems like this is not possible.

  6. Oldham Weather – This is a weekly blog with a 4-day weather forecast (THU-SUN) for Manchester, and its surrounding areas. Weather statistics from the dedicated Vantage Pro 2 weather station in Chadderton, Oldham. Live statistics are updated every 1 minute here, https://www.wunderground.com/personal-weather-station/dashboard?ID=IOLDHAMC2. Follow me on Twitter, @ChadWeather and follow the statistics account, which updates hourly at @OldhamWXStats.
    Oldham Weather says:

    This is superb and I’ve just put it into action. Cheers Chris.

  7. Thanks for your detailed explanation and solution. I do have a problem though in making the advanced dynamic title work in my own report. To compare I opened up your .pbix file and I find the same errors here. It has to do something with the TopN function but I can’t figure out what it is. Also in the Return statement there are two syntax errors. See screenshot of it here: http://tinypic.com/r/334qghu/9
    This is in your file without changes.

    Is this because of an update of Power BI? Or is something wrong in my settings or something? I feel like I’m missing something very obvious here but I’ve been trying it now for hours and can’t wrap my head around it, so I was hoping you could help.

    1. Chris Webb – My name is Chris Webb, and I work on the Fabric CAT team at Microsoft. I blog about Power BI, Power Query, SQL Server Analysis Services, Azure Analysis Services and Excel.
      Chris Webb says:

      You may need to swap the commas in my code for semi-colons ( ; ) if you aren’t using an English language version of Power BI Desktop.

  8. Hi.
    How to check if no value is selected in slicer? By default ,if no slicer value is selected,it is showing the number of distinct values in slicer? But if want to give some different condition in measures based on no selection in slicer.

    1. Chris Webb – My name is Chris Webb, and I work on the Fabric CAT team at Microsoft. I blog about Power BI, Power Query, SQL Server Analysis Services, Azure Analysis Services and Excel.
      Chris Webb says:

      Having no values selected in a slicer is the same as having all values selected in a slicer, if that helps?

    1. Chris Webb – My name is Chris Webb, and I work on the Fabric CAT team at Microsoft. I blog about Power BI, Power Query, SQL Server Analysis Services, Azure Analysis Services and Excel.
      Chris Webb says:

      Not that I know of, sorry

  9. this solution seems to work fine with one slicer, but if a second slicer/filter appears on page, then it kind of breaks in my experience, no longer able to reliably determine when a given number of selections in a slicer is equal to the total possible for that slicer

    1. Chris Webb – My name is Chris Webb, and I work on the Fabric CAT team at Microsoft. I blog about Power BI, Power Query, SQL Server Analysis Services, Azure Analysis Services and Excel.
      Chris Webb says:

      I guess that’s because I’m using the ALL() function in the NumberOfPossibleDays variable. If there is another slicer, you’ll need to change the expression to find the number of possible selections with the filter applied by other slicers.

  10. this solution seems to work fine with one slicer, but if a second slicer/filter appears on page, then it can break in my experience, no longer able to reliably determine when a given number of selections in a slicer is equal to the total possible for that slicer

  11. This is a great post Chris – thank you very much. My question may be very much related to the post above – where the report has multiple slicers. In my situation, I have slicers for Store Name, and slicers for Product Types. Not all stores have the same product types. My DAX measure looks like this:

    Title =
    var SelectedProductTypes =
    VALUES(OrderItem[InternalProductType])

    var NumberOfSelectedProductTypes =
    COUNTROWS(SelectedProductTypes)

    var NumberOfPossibleProductTypes =
    COUNTROWS(ALL(OrderItem[InternalProductType]))

    RETURN

    “Sales reflected for: ”
    & CONCATENATEX(
    VALUES(Store[Name]),
    Store[Name], “, “) & ” – ” &
    IF(NumberOfSelectedProductTypes = NumberOfPossibleProductTypes, “All Product Types”,
    CONCATENATEX(
    SelectedProductTypes,
    OrderItem[InternalProductType], “, ”
    ))

    Expected output where only a Store Name has been selected in the slicer would be:
    STORE NAME – All Product Types

    However, the output is returning all of the individual product types because (I believe) the filter context from the Store Name slicer isn’t recognized. That is, the variable NumberOfPossibleProductTypes is always going to be all of the types across all stores, not the possible types filtered to that particular Store Name. Is there a workaround for this situation?

    Thanks very much.

    1. shouldnt u use a distinctcount in the VAR NumberOfPossibleDays? because you only want the unique values of the day of the week. not all rows.

      so something like:
      VAR NumberOfPossibleValues =
      CALCULATE(COUNTROWS( VALUES(‘Datum'[Week])), ALL(‘Datum'[Week]) )

      1. Chris Webb – My name is Chris Webb, and I work on the Fabric CAT team at Microsoft. I blog about Power BI, Power Query, SQL Server Analysis Services, Azure Analysis Services and Excel.
        Chris Webb says:

        No, because when you use the All() function on a column you only get the distinct values in that column (with filter context removed, of course).

  12. Might be worth a mention – the Measure needs to have the Data Type of “Text”, perhaps it’s an issue with using a Live Connection and doing this, my measure is simply: “Arrears Banding Summary for ” & MAX( ‘Financial Calendar'[Calendar Year Month] ) where [Calendar Year Month] is of the form “2019-Mar” for instance.

    1. Chris Webb – My name is Chris Webb, and I work on the Fabric CAT team at Microsoft. I blog about Power BI, Power Query, SQL Server Analysis Services, Azure Analysis Services and Excel.
      Chris Webb says:

      The announcement blog post does mention this 🙂 https://powerbi.microsoft.com/en-us/blog/power-bi-desktop-april-2019-feature-summary/#dynamicTitles …but I agree, it’s not always clear when your expression returns text and when it returns another data type, so it is probably worth a blog post

  13. Eric L – Arizona – I have been actively involved in the management and performance of audits, reviews, business valuations, litigation and consulting engagements throughout my career in public accounting and consulting. In 1996, I became a Certified Public Accountant in California. In 2004, I became certified as a valuation analyst. I've performed the following types of assignments: numerous audits and reviews of manufacturing, distribution, construction, healthcare and SEC clients; merger and acquisition consultation; business and stock valuations; economic damage calculations; post-acquisition purchase price allocation appraisals; goodwill and intangible impairment assessments; forensic accounting analyses; as well as accounting systems consulting. Co-authored comments to the Financial Accounting Standards Board on behalf of the Construction Financial Management Association and written several articles related to business management, financial reporting, and tax planning. I acquired manufacturing operations in the capacity of Chief Financial Officer and supported the prospective sale of operations as a Controller. Previous Experience: After school I spent over eight years with the Singer Lewak LLP and progressed to Senior Manager; held CFO and Controller positions in manufacturing and construction industries as well as a Senior Manager position with an affiliate of Moore Stephens International Ltd. Education: • Bachelor of Arts in Business Administration with a concentration in Accounting, California State University at Fullerton (1993) • Fundamentals Technique & Theory sponsored by National Association of Certified Valuation Analysts (2004) Memberships: • American Institute of Certified Public Accountants (AICPA) • California Society of Certified Public Accountants • Business Valuation, Forensic, and Litigation Services Section of the AICPA • Association of Certified Fraud Specialists, LA Chapter Board Member (2007-2009) • Construction Financial Managers Association (CFMA) (2004-2008) CFMA Orange County Treasurer (2006-2007) CFMA National Accounting and Reporting Committee (2006-2008) Industries and Projects: Financial Reporting Audits, SEC Reporting, Forensic Accounting and Tax Matters for Construction Contractors; Job Shops; Food Processors/Distributors; Real Estate Developers; Medical Practices, Clinics, and IPAs; Telecommunications and Network Hardware; Aircraft Components; Aluminum Extruder; CD and DVD Wholesaler; Commercial Mortgage Lenders; Commercial Wall Systems Contractor; Digital Surveillance Manufacturer; Data Processing Business Units; Footwear Distributor and Manufacturer; Incentive Travel Services; Insurance Claims Administrator; Landfill Treatment Technology; Metal Working & Machine Shops; National Laundromat Chains; Network Solutions Contractor; PC Peripheral Components; Residential Plastering Contractor; Retail Furniture Store; Sound Systems Manufacturer/Distributor; Software Developers; Surgical Services and Equipment
    Eric Lane says:

    Excellent work. Most businesses want a “3 line title” 1) Entity 2) Report Name 3) Date or Date Range This technique is very important for reports to meet a traditional business standard.

  14. Hi

    Any idea as to why when I click on the code found in Title2 under DimDate and try to copy the formula – it gives me an error with incorrect syntax.

    The syntax for ‘SelectedDays’ is incorrect. (DAX(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))).

    if i don’t click on the formula – it works fine.

    Even if i try to replicate this method it gives me errors.

    Regards

    1. Chris Webb – My name is Chris Webb, and I work on the Fabric CAT team at Microsoft. I blog about Power BI, Power Query, SQL Server Analysis Services, Azure Analysis Services and Excel.
      Chris Webb says:

      The error suggests you have not pasted the whole definition of the measure – the first line is missing from the code you give here.

      1. Hi

        Thanks for your reply.

        But, if i look at the report template – the first line is Title2 = Var SelectedDays.

        When i open the report – it works fine.

        The issue is if i click anywhere in the dax code or even select all and copy it – its breaking.

        Regards

      2. Chris Webb – My name is Chris Webb, and I work on the Fabric CAT team at Microsoft. I blog about Power BI, Power Query, SQL Server Analysis Services, Azure Analysis Services and Excel.
        Chris Webb says:

        That’s strange- it must be a bug in Power BI or something

      3. Thanks – i tested it on another version of powerbi and it works fine.
        seems like there is something corrupted on my power bi desktop.

        Regards

  15. Hi Chris,

    I’ve been trying to use a switch function in a dynamic title – but the measure becomes unavailable to use in the dynamic title. Is there a way around this, as the other option is many many if statements.

    Cheers

    Mark

  16. Hi Chris,
    Just awesome post and fantastic way of doing dynamic chart titles.

    Just a small question (might be stupid one!) from my end, when 2 year selected (i.e., 2020 and 2019). It shows latest year first and then previous year (2020 and 2019).

    May be I am doing something wrong. Any suggestions, ideas, thoughts, welcome!

    Regards,
    Deepak

  17. Hi Chris,

    This is fantastic! Is there a way to do this with a time filter/slicer? i.e. if months Jan, Feb, Mar and Apr are all selected for 2020 it’ll show ‘Jan-Jun 2020’?

    Thanks 🙂

  18. Hi – Similar to the above, this is really helpful!

    Can you advise what would be the best way to add a second field to the title – eg [Month] and [Year] ?

  19. Hi, Chris!

    I am trying to get what was set in a filter – NOT the resulting selected items in the table. For example, we are using Advanced filtering to show items greater than or equal to 10 and items less than or equal to 200. However, the max item in the table is 150. So, using anything related to SELECTEDVALUE, etc., brings back the max in the table (150) – NOT what the user entered into the filter (200). If my title shows the MIN and MAX of what is in the table (i.e., “Average of values between 10 and 150” vs “Average of values between 10 and 200”), the user thinks they set the filter incorrectly and it causes confusion. Is there a way to display what is actually showing in the filter? I have found nothing on this anywhere. Maybe I am just not searching using the right words? Thanks for any pointers on this!!

    1. Chris Webb – My name is Chris Webb, and I work on the Fabric CAT team at Microsoft. I blog about Power BI, Power Query, SQL Server Analysis Services, Azure Analysis Services and Excel.
      Chris Webb says:

      Hi Jennifer – you won’t be able to do this, unfortunately. There’s no way to get what the user actually entered in the filter.

Leave a ReplyCancel reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.