DAX: How to count slicer values after report-level filter but ignore slicer selection?

[PratikShetty]

Hi everyone,

I'm trying to write a DAX measure that lists selected slicer values from the 'Country'[Country] column. Here's the behavior I want:

• If none of the slicer values are selected → return "ALL"

• If all available slicer values are selected → return "ALL"

• If some values are selected → return only the selected country names

The catch is:
I have a report-level filter applied on 'Country'[Country] (e.g. only 4 countries are visible in the slicer). I want to respect this report-level filter but ignore slicer selections when calculating the total number of available countries.

Here’s the DAX I'm using:

```

A_Filter_Country =

VAR Selected = VALUES('Country'[Country])

VAR SelectedCount = COUNTROWS(Selected)

VAR VisibleCount =

CALCULATE (

        COUNTROWS ( VALUES ( 'Country'[Country] ) ),

        REMOVEFILTERS ( 'Country'[Country] )

    )

RETURN

"SelectedCount : " & SelectedCount & UNICHAR(10) &

"VisibleCount : " & VisibleCount

// IF(

//     SelectedCount = 0 || SelectedCount = VisibleCount,

//     "ALL",

//     CONCATENATEX(

//         Selected,

//         ''Country'[Country],

//         ", " & UNICHAR(10)

//     )

// )

```

❌ The Problem:

Even though the report-level filter limits the countries to 4 (e.g. France, Germany, Mexico, USA), the VisibleCount always returns the full list (e.g. 5), because REMOVEFILTERS removes the report filter too.

✅ What I Need:

A way to calculate the number of countries visible in the slicer (after report-level filtering), but not affected by the slicer selection.

Any ideas on how to achieve this in DAX? Please donwload the sample .pbix file here.

Thanks in advance!

Best Regards,
Pratik

[v-achippa]

Hi @PratikShetty,

Thank you for reaching out to Microsoft Fabric Community.

Thank you for sharing the detailed explanation, the issue here is the REMOVEFILTERS removes all filters including report/page level filters. That is why the VisibleCount shows all 5 countries from the table instead of the 4 visible in the slicer. Please use this below measure:

A_Filter_Country =

VAR Selected = VALUES('Country'[Country])

VAR SelectedCount = COUNTROWS(Selected)

VAR VisibleCountries = CALCULATETABLE (VALUES('Country'[Country]), ALLSELECTED('Country'[Country]))

VAR VisibleCount = COUNTROWS(VisibleCountries)

RETURN

IF (SelectedCount = 0 || SelectedCount = VisibleCount,

    "ALL", CONCATENATEX (Selected, 'Country'[Country], ", " & UNICHAR(10)))

This works correctly based on your requirement and keeps the slicer’s behaviour consistent with report level filters.

If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it! 

Thanks and regards,

Anjan Kumar Chippa

[johnt75 · Accepted solution]

Create a new table like

Country For Filter = DISTINCT( Country[Country] )

Create a one-to-many relationship from this new table to Country, so that 'Country for Filter' filters Country. Use this table to place the report level filter.

You can now create a measure like

Selected Countries = 
VAR NumAllCountries = CALCULATE(
    COUNTROWS( Country ),
    REMOVEFILTERS( Country ),
    VALUES( 'Country For Filter'[Country] )
)
VAR NumSelected = COUNTROWS( VALUES( Country[Country] ) )
VAR Result = IF( NumSelected = NumAllCountries, "All",
    CONCATENATEX( VALUES( Country[Country] ), Country[Country], ", " )
)
RETURN Result

This works by removing the filters from Country, which also removes filters from 'Country for Filter' because it is in the expanded table, but then reapplying the filter from 'Country for Filter'.

[PratikShetty]

Hi @johnt75 ,

Your solution worked. I only needed to add the Filter table and the DAX worked.

Thanks a lot for guiding me.

Best Regards,

Pratik