Most PBI reports and dashboards contain filters/slicers. It then becomes very important to ensure that your users are aware of what filters are applied. So this workout is focused on that. For those of you new to DAX, this should be a great workout as it doesn’t require alot of coding; for the experienced ones, it’ll be a great way to provide others with a technique that can be added to reports.
In this situation, there is a chart showing the number of sales by product. A slicer is also provided that allows you to select one or more countries. The problem is that the slicer is a drop down and thus it’s not readily visible as to what counties might be selected.
This workout has two parts.
The first part is around creating a DAX measure that will display all the countries that are selected (or displaying “All Countries Selected” if this is the case. As an added step, have the list be sorted alphabetically. In the attached PBI file, I have added a simple Table visual to test this out, however, other visuals (such as the Card visual, smart narrative, etc.) are also great ways to display this.
The second part is to create a second DAX measure that can be used in the “Subtitle” for the chart. However, rather than list out multiple countries (as above), some additional criteria is provided. The second and third pictures provide an example of this as well.
Submission
Load the supplied PBI file, create your solution, and reply to this post. Upload a screenshot of your solution (or the PBI file) along with any DAX items. Please blur any solution or place it in a hidden section.
Hmm, I can’t seem to understand how can I maybe sort the name of countries selected. Would love to see how people solve this.
Maybe we can make another table at Data Level with Distinct Country names and use that? Not sure.
Rest is quite done.
DAX for Countries
Countries Selected =
IF(
ISFILTERED(Regions[Country]),
CONCATENATEX
(
ALLSELECTED(Regions[Country]), Regions[Country], ", "
),
"All Countries are Selected"
)
DAX for Subtitle
Subtitle =
IF(
ISFILTERED(Regions[Country]),
SELECTEDVALUE(Regions[Country], "Multiple Countries"),
"All Countries"
)
Country Filters Applied =
VAR _SelectedCountries = DISTINCT ( Regions[Country] )
VAR _Result =
IF (
COUNTROWS ( _SelectedCountries ) = COUNTROWS ( ALL ( Regions[Country] ) ),
"All Countries Selected",
TOCSV ( _SelectedCountries, -1, , FALSE() )
)
RETURN
_Result
I find this function convenient when I want to display multiple columns (or quickly debug a table variable) but it has the limitation of not allowing for sorting and it always outputs a new line for each item rather than allowing for custom delimiters between table rows, so the output looks like this:
You are kidding me!
Oh my God, why did I never know that, lol!
Didn’t even notice this in the intelligense while writing the code. Thank you so much for pointing this out!
I find TOCSV enormously useful for debugging - one of my favorite feature adds from last year.
I haven’t tried this, and not sure it would be worth the headache, but could you harvest the number of countries selected, build a one column TOPN table of that number of rows sorted by country name, and then send that table to TOCSV?
Great, practical workout. I’ve seen @Greg use this technique very effectively in his reports with multiple slicers to mirror the slicer selections at the bottom of the page in a small row - very useful in particular when using a popout slicer panel.
Here’s my solution:
Show Countries Filtered measure
Country Filters Applied =
VAR SelCountries =
DISTINCT( Regions[Country] )
VAR AllCountryNum =
COUNTROWS( ALL( Regions[Country] ))
VAR Result =
IF(
COUNTROWS(SelCountries) = AllCountryNum,
"All Countries Selected",
CONCATENATEX(
SelCountries,
Regions[Country],
", ",
Regions[Country],
ASC
)
)
RETURN Result
Subtitle measure
Subtitle =
VAR SelCountryCount =
COUNTROWS( DISTINCT( Regions[Country] ))
VAR AllCountryCount =
COUNTROWS( ALL(Regions[Country] ))
VAR Result =
SWITCH(TRUE(),
SelCountryCount = 1, SELECTEDVALUE( Regions[Country] ) & " Selected",
SelCountryCount = AllCountryCount, "All Countries Selected",
"Multiple Countries Selected"
)
RETURN Result
I considered using TOPN but, while it allows for an Order By expression for choosing which rows to return, the documentation states that “TOPN does not guarantee any sort order for the results.”
Have you seen this video? Would be an insane level of effort to implement this as an alternative to CONCATENATEX, but it does seem possible to guarantee the sort order of the TOPN table feeding into TOCSV:
Selected Countries V1 =
VAR SelectedCountries = DISTINCT(Regions[Country])
// Distinct list of all countries
VAR _Result =
IF(
COUNTROWS(SelectedCountries) = COUNTROWS(ALL(Regions[Country])),
"All Countries Selected",
// If the number of rows n SelectedCountries is equal to all rows in Regions[Country] then All Countries Selected
CONCATENATEX(SelectedCountries, Regions[Country], " | ", Regions[Country], ASC)
// If not concatenate the names of the selected countries in a string and sort ascending based on their names
)
RETURN _Result
Subtitle measure:
Subtitle =
IF(
HASONEVALUE(Regions[Country]),
// Checks if only one country is selected
VALUES(Regions[Country]),
// If it is it returns the name of the country using VALUES
IF(
ISFILTERED(Regions[Country]),
"Multiple Countries",
"All Countries"
)
)
A merged option which will actually show the Selected countries as a subtitle:
Merged =
VAR SelectedCountries = DISTINCT(Regions[Country])
VAR _Result =
IF(
COUNTROWS(SelectedCountries) = COUNTROWS(ALL(Regions[Country])),
"All Countries Selected",
CONCATENATEX(SelectedCountries, Regions[Country], " | ", Regions[Country], ASC)
)
VAR _Subtitle =
IF(
HASONEVALUE(Regions[Country]),
VALUES(Regions[Country]),
IF(
ISFILTERED(Regions[Country]),
_Result,
"All Countries"
)
)
RETURN _Subtitle
I know I am late but still posting my entry for the workout :
DAX CODE
Selection =
VAR _totcount= CALCULATE(DISTINCTCOUNT(Regions[Country]),ALL(Regions[Country]))
VAR _selcountry= VALUES(Regions[Country])
return
if (_totcount=COUNTROWS(_selcountry),"All countries is selected",
CONCATENATEX(_selcountry,[Country],",",[Country],ASC))
Subtitle =
VAR _totcount= CALCULATE(DISTINCTCOUNT(Regions[Country]),ALL(Regions[Country]))
VAR _selcountry= SELECTEDVALUE(Regions[Country],
if (DISTINCTCOUNT(Regions[Country])=_totcount,"All countries "
,"Multiple Countries" ))
return
_selcountry
