Understanding if the user has selected all options on a slicer

[ValeriaBreve]

Hello,

I am trying to figure out a way to understand whether a user has selected all items on a slicer or no items at all.

If I use the formula 

ISFILTERED( D_ProductionLinesWithCC[ProductionLineCodeCC] )

it returns true if all items are sliced but 1. As soon as the user selects all items, it returns false as if none were selected.

Is there  a way to tell whether the user is actively engaging with the slicer and selecting all items?

I am asking because I have a master slicer on top, and if I don't handle this, PowerBI considers that all items in the subslicer are selected if if the user has not touched them yet.

I am using the new button slicer visual.

Thanks!

Kind regards

Valeria

[v-veshwara-msft]

Hi @ValeriaBreve ,
Thanks for reaching out to Microsoft Fabric Community.
Just wanted to check if the responses provided were helpful. If further assistance is needed, please reach out.
Thank you.

[Praful_Potphode]

Hi @ValeriaBreve 
try below measure:

Is Slicer Engaged = 
VAR TotalItems = COUNTROWS(ALL(D_ProductionLinesWithCC[ProductionLineCodeCC]))
VAR FilteredItems = COUNTROWS(FILTERS(D_ProductionLinesWithCC[ProductionLineCodeCC]))

RETURN
IF(
    ISFILTERED(D_ProductionLinesWithCC[ProductionLineCodeCC]) && FilteredItems = TotalItems,
    "User Manually Selected All",
    IF(
        ISFILTERED(D_ProductionLinesWithCC[ProductionLineCodeCC]),
        "User Selected Some",
        "No Interaction (Defaults to All)"
    )
)

Output can be configured based on requirements.

Please give kudos or mark it as solution once confirmed.

Thanks and Regards,

Praful

[ValeriaBreve]

Hello @Praful_Potphode and sorry for the late reply - Christmas break! Unfortunately the solution does not work - PowerBI does not see the difference between when the user selects no unit and when it selects all of them. It does of course work correctly when some units are selected - just not all of them. 

[cengizhanarslan]

ISFILTERED() can’t distinguish “no selection” from “all selected”, because in both cases the column ends up effectively unfiltered.

The usual way is to compare how many values are currently visible vs how many exist in the full list.

Slicer State =
VAR SelectedCnt =
    COUNTROWS ( ALLSELECTED ( D_ProductionLinesWithCC[ProductionLineCodeCC] ) )
VAR TotalCnt =
    COUNTROWS ( ALL ( D_ProductionLinesWithCC[ProductionLineCodeCC] ) )
RETURN
SWITCH(
    TRUE(),
    SelectedCnt = 0,          "None",          -- rare, but possible if other filters remove everything
    SelectedCnt = TotalCnt,   "All (or untouched)",
    "Partial"
)

[ValeriaBreve]

Hello @cengizhanarslan and sorry for the late reply. In my case, unfortunately this method cannot work, as the number of items in the full list are the same as the number of selected items if the user selects all of them. But I'll keep it mind for the future, in case I have this scenario!

[Ahmed-Elfeel · Accepted solution]

Hi @ValeriaBreve,

I hope you are doing well today☺️❤️

So here is some points:

• DAX can't distinguish between Select All and untouched slicer (This is a limitation of the Power BI engine)
• Your DAX formula ISFILTERED( D_ProductionLinesWithCC[ProductionLineCodeCC] ) only tells you whether the column is filtered not how or by whom
• So when all values are in context ISFILTERED() correctly returns FALSE because from the engine POV there is no effective filter on that column (This is the normal behavior of power bi)

Here is Some Approaches you can try:

First Approach: I highly recommend it and used it before

You can use a disconnected control table (We call it Mode Slicer)

• First create a small disconnected table (Use this slicer to let the user explicitly choose the behavior):

Valeria ModeSlicer =
DATATABLE(
    "Mode", STRING,
    {
        { "Auto (Use Master)" },
        { "Manual" }
    }
)

• Then branch your logic:

Smart Measure =
VAR Mode = SELECTEDVALUE(Valeria ModeSlicer[Mode], "Auto (Use Master)")
RETURN
SWITCH(
    Mode,
    "Auto (Use Master)",
        CALCULATE([Base Measure], REMOVEFILTERS(SubSlicer[Column])),
    "Manual",
        [Base Measure]
)

Second Approach: Bookmark + button toggle (UX clarity Approach)

Just Create 2 Bookmarks and Buttons to toggle between the modes

• Bookmark A sub slicer ignored (default state)

• Bookmark B (sub slicer active)
• Buttons toggle between modes

Final Approach:

This not an approach but is what you thinking about or your business (Design Approach)

• So If you dont want to do all of above Approaches Stop trying to detect user clicks and build your report around Power BI Limitations
• The Core of this Approach idea is to treat All Selected and Untouched as the same valid state (No active filter)

To Apply this you need first to apply the rules for your sub slicer and measures like:

• Partial master selection > sub slicer filters normally
• All master items (untouched or Select All) > sub slicer works independently

Slicer Has Effect :=
VAR SelectedCount = COUNTROWS(VALUES(SubSlicer[Column]))
VAR TotalCount = COUNTROWS(ALL(SubSlicer[Column]))
RETURN SelectedCount < TotalCount

Smart Measure :=
IF([Slicer Has Effect],
    [Base Measure],                  -- apply sub-slicer
    CALCULATE([Base Measure], REMOVEFILTERS(SubSlicer[Column]))  -- ignore slicer
)

Final Thought (Advice):

• If you want more control and you design this for entrprise (like me) use the First Approach
• If you want the best UX clarity use the Second Approach
• If you want the simplest solution and dont want to fight Power BI😅 use the Final Approach

if this post helps, then I would appreciate a thumbs up and mark it as the solution to help the other members find it more quickly.

[ValeriaBreve]

Thank you @Ahmed-Elfeel ! This is very clear. 🙂 

[GeraldGEmerick]

@ValeriaBreve You could do a COUNTROWS( 'Table' ) and an COUNTROWS( ALL( 'Table' ) ). If they are equal to one another then all items have been selected.

[ValeriaBreve]

Hello @GeraldGEmerick thank you for the reply! In my case unfortunately this does not work as if nothing is selected the countrows will be exactly the same as if everything is selected, as there are no filters applied to the table....