Power BI Blog: Dynamic Headers
17 June 2021
Welcome back to this week’s edition of the Power BI blog series. This week, we will look at dynamic titles, and how to create titles with multiple linked values.
In the past, we have looked at how to create a dynamic heading based on a slicer selection. In today’s article, we’re going to see how we can add multiple selected items to a heading.
In today’s example, we’re going to create a simple chart that looks at Sales broken down by specified countries. We have a slicer that we can use to filter our data.
Now, we want to create a heading. If we were to use the code that we had previously, we would just end up with the first non-blank value, which is Australia in this case:
"Selected Country: " & FIRSTNONBLANK(CountryList[EnglishCountryRegionName],1),
"No Country Selected"
This clearly isn’t going to work – FIRSTNONBLANK effectively aggregates our three selected options, but only chooses the first of the list. Instead, we need to use an aggregation that combines our results. We can use CONCATENATEX to do this:
List of Countries = CONCATENATEX(CountryList,CountryList[EnglishCountryRegionName]," / ")
CONCATENATEX gives us the ability to create a string by combing values together. Importantly, it also allows us to specify the delimiter that we want to insert between the different values. We also have the ability to specify a sort order – by default, it will use alphabetical order of the values we’re using, but we can link it back to other measures, such as our Total Sales measure:
List of Countries = CONCATENATEX(CountryList,CountryList[EnglishCountryRegionName]," / ",[Total Sales],DESC)
All we need to do now is drop the result into a Card visual, pop in the measure, and line up our results:
There we go – a neat way to display multiple options from a Slicer.
In the meantime, please remember we offer training in Power BI which you can find out more about here. If you wish to catch up on past articles, you can find all of our past Power BI blogs here.