Search code examples
powerbidaxpowerbi-desktop

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


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:

enter image description here

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

enter image description here

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

The measure 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!!


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
        )