Power BI Dynamic Title - how to return "All" if all slicer options are selected

[kkwang]

Hi, 

The Title I want is: when I select every province, the title would show the same as nothing selected.

For example, when nothing is selected, the title looks like this: Weekly Report - All Provinces

If I select eveything, right now the title looks like this: 

But I want it return the same as above: "Weekly Report - All Provinces"

-------------------------------------------------------------------------------

The mesuare I created: 

Title =  "Weekly Report - "

&  IF(NOT(ISFILTERED(DIMENSION[Province])),"All Provinces" , IF( ISFILTERED(DIMENSION[Province]), CONCATENATEX ( VALUES (DIMENSION[Province]), DIMENSION[Province], "," )))

------------------------------------------------------------------------------

Can someone kindly take a look and help me fix this? Thanks so much in advance!!

[qqqqqwwwweeerrr]

Hi @kkwang 

try using the method shown in this video, there might be few modifications are required: https://youtu.be/w6vtCEQ64Kk

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!

Reagrds

[kpost]

This solution has the drawback of using a hard-coded number of maximum selection values.  In the example given, the maximum is 4 because there are 4 in the slicer.  

If one were to add more options to the slicer, then they have to also change the measure.

[kpost]

Here is the DAX you are looking for.  I have also attached a .pbix file with a working solution.

Title =

var province_list= IF(

    ISFILTERED('Dimension'[Province]),

    CONCATENATEX(

        VALUES('Dimension'[Province]),

        'Dimension'[Province], ", "

    ),

    "All Provinces"

)

var distinct_count = CALCULATE(DISTINCTCOUNT('Dimension'[Province]), ALL('Dimension'[Province]))

var comma_count = LEN(province_list) - LEN(SUBSTITUTE(province_list, ",", ""))

return

    SWITCH(

        TRUE(),

        comma_count = 0 && province_list = "All Provinces", "Weekly Report - All Provinces",

        comma_count + 1 = distinct_count, "Weekly Report - All Provinces",

        "Weekly Report - " & province_list

    )

Mediocre Power BI Advice, but it's free

[kkwang]

Thanks soooo much!!! It's working perfectly now!!!!

[kpost]

Happy to help!

[kkwang]

Hi, thanks for your help!! but the measure still doesn't work unfortunately.... 

[kpost · Accepted solution]

Ahhh I see,  here you go.  I have edited my solution above to include this change.  It works by looking at the number of commas in the list and comparing that to the total # of provinces to detect when ALL provinces are selected.  I also attached a .pbix file with the solution.

Title =

var province_list= IF(

    ISFILTERED('Dimension'[Province]),

    CONCATENATEX(

        VALUES('Dimension'[Province]),

        'Dimension'[Province], ", "

    ),

    "All Provinces"

)

var distinct_count = CALCULATE(DISTINCTCOUNT('Dimension'[Province]), ALL('Dimension'[Province]))

var comma_count = LEN(province_list) - LEN(SUBSTITUTE(province_list, ",", ""))

return

    SWITCH(

        TRUE(),

        comma_count = 0 && province_list = "All Provinces", "Weekly Report - All Provinces",

        comma_count + 1 = distinct_count, "Weekly Report - All Provinces",

        "Weekly Report - " & province_list

    )