Would it not be nice if your Title changed based on what you selected on your slicer?

In this example below I am going to show you how I used a Country slicer to dynamically change the title of my card based on my slicer selections.

This was really useful because the Country Slicer has so many options I am not able to view all the Countries selected on the screen. And by doing it in this way it allowed me to create a more meaningful dashboard, as well as know which Countries had been selected.

As you can see below, this is what it looked like after I had added in my DAX measure and configured the Card. You can click on the Country Slicer to see the changes.

Creating the DAX Measure to display the Country Names

Below is the DAX syntax that I created so that it would display the Country Names based on the following criteria

  • If no Countries were selected in the Slicer then display “No Countries Selected”
  • If one Country was selected in the Slicer then display “Country: [Country Name]”
  • If less than or equal to 4 four Countries were selected in the Slicer then display “Countries: [Country Name1], [Country Name2], [Country Name3], [Country Name3] “
  • Otherwise (meaning that more than 4) Countries were selected in the Slicer then Display “Countries: [Country Name1], [Country Name2], [Country Name3], [Country Name3] and more …”

Here is the Syntax below.

NOTE: The comments are highlighted in Green below as this syntax is quite long. Whilst any other explanations will be explained afterwards.

Slicer Label =
/* Below is getting the Distinct Count of Country */
VAR CountryCount =
 DISTINCTCOUNT ( 'Working Hours'[Country] )
/* Below is getting the Distinct Count of all the Country Names
 So each Country is only being counted for once across the entire dataset. */
VAR MaxCountryCount =
 CALCULATE (
 DISTINCTCOUNT ( 'Working Hours'[Country] ),
 ALL ( 'Working Hours'[Country] )
 )
/* This is to specify how many Countries I wanted to show before stopping
 the contatenation and ensuring it easily readable */
VAR MaxItemsToShow = 4
RETURN
 SWITCH (
 TRUE (),
     /* If only 1 country is selected then show the following */
 CountryCount = 1, "Country: " & VALUES ( 'Working Hours'[Country] ),
     /* If the Country Count is the same as the Maximum Count of all the Countries
 Then assume nothing has been selected */
 CountryCount = MaxCountryCount, "No Countries selected",
     /* If CountryCount is less than and equal to the MaxItems to show (Current
 Example = 4, then show the top 4 values */
 CountryCount <= MaxItemsToShow, "Countries: "
            & CALCULATE ( 
                CONCATENATEX (
                    TOPN ( MaxItemsToShow, VALUES ( 'Working Hours'[Country] ) ),
                    'Working Hours'[Country],
                    ", "
                )
    ),
 /* Else concatenate the first 4 values and show that there are more
 Values selected */
 "Countries: "
 & CALCULATE (
 CONCATENATEX (
 TOPN ( MaxItemsToShow, VALUES ( 'Working Hours'[Country] ) ),
 'Working Hours'[Country],
                 ", " )
                ) & " and more..."
 )
  • An overview of the above is the variables at the start.
  • And then the SWITCH syntax in which I looked for a specific CountryCount condition and then based on that I provided an associated result.

If you wanted to apply this to your own data you can simple change the sections above highlighted in BLUE

  • Replace your slicer value with my value which based on the example is:
    • ‘Working Hours'[Country]
  • Which will be your ‘Table Name'[Column Name]
  • And then replacing the Singular and Multiple Text values highlighted in ORANGE above.

Now that I have completed my DAX measure for the changing slicer values below is what it does when I have no countries selected.

And now when I select 4 countries or less it displays all the Country names

And if I had to select a fifth country it would then display “and more …”

Conclusion

I have demonstrated how to create a measure which will make your data more meaningful to your end users. And by simplifying the process using Variables, it is also a lot easier to customize for your own requirements.

Any suggestions or questions please let me know.