Use slicer as selector - show no selections when no slicer option active

[zahin_maisa]

I want to use a slicer as a selector, displaying no selections when no slicer option is checked, and all when select all is checked.

I use the following measure to count no. of selections:

SelectedOptionCount =
VAR SelectedOptions = COUNTROWS (ALLSELECTED (...))
VAR TotalOptions = COUNTROWS (ALL((...)))
RETURN
IF (SelectedOptions = TotalOptions||ISBLANK(SelectedOptions), 0, SelectedOptions)

The card on the right has field SalesOption. The filter, SelectOptionCount > 0, on it ensures no selections show when the slicer is inactive. However, SelectOptionCount is 0 when I select all with my current measure: 

[johnt75]

Try

IF ( (SelectedOptions = TotalOptions && NOT ISFILTERED(...) ) ||ISBLANK(SelectedOptions), 0, SelectedOptions)

[zahin_maisa]

The measure displays as 0 in the card, but the filter >0 doesn't work for some reason

[v-achippa]

Hi @zahin_maisa,

Thank you for reaching out to Microsoft Fabric Community.

Try this below measure:

SelectedOptionCount =
VAR SelectedOptions = COUNTROWS(VALUES('Sales'[SalesOption]))
VAR TotalOptions = COUNTROWS(ALL('Sales'[SalesOption]))
RETURN
SWITCH(
TRUE(),
SelectedOptions = 0, BLANK(), 
SelectedOptions = TotalOptions && NOT HASONEFILTER('Sales'[SalesOption]), BLANK(), 
SelectedOptions 
)

This will Return BLANK() when nothing is selected or when Select All is selected but no real filter is applied. Return count when actual filtering is done.

Apply a visual level filter SelectedOptionCount is not blank. This ensures the visual appears only when the user actively selects options.

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

[zahin_maisa]

I want to distiguish between 'Select all' and select nothing. This formula is more complicated but results in this: 

[v-achippa]

Hi @zahin_maisa,

Here is the updated measure, it will distinguish all the cases:

SelectedOptionStatus =

VAR SelectedCount = COUNTROWS(VALUES('Sales'[SalesOption]))

VAR TotalCount = COUNTROWS(ALL('Sales'[SalesOption]))

RETURN

SWITCH(

    TRUE(),

    SelectedCount = 0, "Nothing Selected",

    SelectedCount = TotalCount && NOT ISFILTERED('Sales'[SalesOption]), "All Selected (No Filter)",

    SelectedCount = TotalCount, "All Selected (Manual)",

    "Filtered Selection"

)

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

[zahin_maisa]

It always returns 'Filtered Selection'. I believe that because this slicer is affected by another in a previous page. Is there a workaround?

[v-achippa · Accepted solution]

Hi @zahin_maisa,

Please try this below approach:

Create a disconnected table and use this in the slicer instead of the original one i.e 'Sales'[SalesOption]. Now use this below measure to detect the slicer status:

SelectedOptionStatus =

VAR TotalOptions = COUNTROWS(ALL('SalesOptions_Disconnected'))

VAR SelectedOptions = COUNTROWS(VALUES('SalesOptions_Disconnected'))

RETURN

SWITCH(

    TRUE(),

    SelectedOptions = 0, "Nothing Selected",

    SelectedOptions = TotalOptions, "All Selected",

    SelectedOptions < TotalOptions, "Filtered Selection",

    "Other"

)

This avoids interaction from other slicers or pages and gives the expected output.

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

[bhanu_gautam]

@zahin_maisa , Try using

DAX
SelectedOptionCount =
VAR SelectedOptions = COUNTROWS (ALLSELECTED (...))
VAR TotalOptions = COUNTROWS (ALL((...)))
RETURN
IF (SelectedOptions = TotalOptions, TotalOptions, IF(ISBLANK(SelectedOptions), 0, SelectedOptions))