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):
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:
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:
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.
Genius. Thanks, I am implementing this right now!
good work as usual. Thanks 🙂
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.
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.
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!
did you find a workaround for this color changing bars for selected filters?
Hi Chris excellent post !!!!
Did you know a technique to pre select the CurrentDay or CurrentYear in a slicer
Thanks !
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
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
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
Good stuff, mate.
Many thanks, so helpful!
“the more I learn, the less I feel actually know”
Awesome post. Is there a way to wrap the text. I want to show each value selected on the next line. Thanks in Advance
Hmm, good question – it seems like this is not possible.
This is superb and I’ve just put it into action. Cheers Chris.
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.
Apologies, tinypic is a horrible site with all those ads. Same screenshot on better host here: https://imgur.com/a/phYqi
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.
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.
Having no values selected in a slicer is the same as having all values selected in a slicer, if that helps?
HI thats was perfect but when i have two different slicers then how should i implement it .
Is Microsoft working on a simple solution for dynamic titles?
Not that I know of, sorry
Hi Chris…This is awesome post. In my case, I have “Select All” option also. How to handle that case ?
Hi Chris….Is there any way to handle “Select All” option in case ?
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
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.
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
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.
shouldnt u use a distinctcount in the VAR NumberOfPossibleDays?!
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]) )
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).
Very nice, Looking for more short and simple techniques. Thanks
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.
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
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.
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
The error suggests you have not pasted the whole definition of the measure – the first line is missing from the code you give here.
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
That’s strange- it must be a bug in Power BI or something
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
Hi Chris, Is there any similar workaround for Dynamic Measure Display Name (for e.g. – PY should display as 2018) ?
Thanks a lot Sir for this wonderful post. It helped me fix the missing piece on my Dashboard!
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
Hi Chris,
this worked like magic.
Is there a way to show a static title if everything is selected from the drop down?
Bst,
Samvedna
Thanks Chris – relevant still today 🙂
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
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 🙂
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] ?
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!!
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.