Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Re slicer selection:
Has anyone found a solution to the problem of distinguishing between selecting all the values on a slicer and selecting no values?
Specifically, when the user de-selects the Select All checkbox, all ticks are removed and my users expect that nothing is now selected and therefore any visuals dependent upon the slicer would not show any data. They also expect that, when all values are selected, then all data on the visual is shown.
This is not the slicer's behaviour. It treats all and nothing as 'no filter passed'.
I've seen a few answers on this topic but none of them seem correct. Typical solution is to test HASONEFILTER and/or ISFILTERED in a measure and add it as a visual filter to the visual.
However I'm fairly certain that, in both situations, all values are passed to the visual with the following conditions: HASONEFILTER = TRUE() and ISFILTERED = FALSE()
Are there any other solutions out there? I cannot use custom visuals as I have too many slicers.
I suspect there aren't any, as programmatically we cannot tell the state of the slicer, only the values passed by it. And as they are the same values for both all or nothing, then it seems to me that we can't distinguish.
But I'm hoping I'm wrong!!! 😀
I find that selecting NOTHING in a slicer is a very difficult but not impossible thing to do.
The steps are only practical when the number of items is small. The steps are:
- enable the select all button on the slicer if it is not already enabled
- click the select all button which will cause ALL items to be selected, all checkboxes will be black
- ctrl-click each selected item one at a time which will deselect each item one at a time, checkboxes slowly go white
- when you finally deselect that last one, NOTHING will finally be selected
- if the slicer is displayed as a list, the item check boxes will all be white
- the slicer's select all check box will not be white, nor black, but instead gray in the middle
So far all of my other attempts at selecting NOTHING actually result in select ALL by virtue of clearing selections. It's as if the data is unfiltered. The slicer's select all is white, and all the items are white. The data is not eliminated.
HI @belvoir99,
You can take a look at the following measure formula to use isfiltered and all function to clarify the filter selection status:
Select Status =
VAR _selected =
COUNTROWS ( VALUES ( 'Sample'[Value] ) )
VAR _all =
COUNTROWS ( DISTINCT ( ALL ( 'Sample'[Value] ) ) )
RETURN
IF (
ISFILTERED ( 'Sample'[Value] ),
IF ( _selected <> _all, IF ( _selected > 1, "Multiple", "Single" ), "ALL" ),
"Non"
)
Regards,
Xiaoxin Sheng
Hi Xiaoxin Sheng
This is a very nice piece of code but doesn't quite work because 'select all' ticked or unticked returns 'None'. 'Select all' returns ISFILTERED as false: it isn't filtered as all values are selected. This is correct when ticked. But, when unticked then ISFILTERED still returns false. See below.
Select Status =
VAR _selected =
COUNTROWS ( VALUES ( Geography[GeographyName] ) )
VAR _all =
COUNTROWS ( DISTINCT ( ALL ( Geography[GeographyName] ) ) )
RETURN
IF (
ISFILTERED ( Geography[GeographyName] ),
IF ( _selected <> _all,
IF ( _selected > 1,
"Multiple",
"Single" ),
"ALL"
),
"None"
)
This is correct: all values are selected therefore nothing is filtered.
This is not correct: no values are selected, therefore everything is filtered out.
And just to clarify: if I select all the items individually (but don't tick the 'select all' button) then the measure returns the ALL value which is a different outcome to the first image above.
Is there another method where I can distinguish (per OP) between Select All ticked and Select All unticked?
I think that the problem ultimately is the Slicer visual: when it has nothing selected it should show nothing - instead it shows everything.
Thanks!
Hi @belvoir99,
AFAIK, current filter functions are hard to recognize the difference between not selected and allselected(the default status). So the top two scenarios are equivalent for Dax ISFILTERED function.
My formula can be used to recognize the manually selected on the filter items except 'select all' option.
Regards,
Xiaoxin Sheng
Thanks @v-shex-msft Xiaoxin! I suspected there wasn't anything else possible but it was good to hear from you!
I won't mark this as a solution because it hasn't solved the problem.
Power BI tries to keep separate dashboard objects and their properties from DAX code and the data. However, it means that it is very limited in manipulating objects according to data value (via DAX).
@PickleBear I have well over 1,000 users across 150+ organisations, and training is self-learning!
Putting descriptive labels on a page is a workaround but leaves the screen cluttered.
Thanks anyway but I'm looking for a solution.
Would it be easier to train the user that nothing selected means everything selected? Not sure what the benefit is to seeing the visuals without any data! You could create a measure that tells people what is selected on the page and put this in a card next to the slicer - for example
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
115 | |
100 | |
88 | |
68 | |
61 |
User | Count |
---|---|
152 | |
120 | |
102 | |
87 | |
68 |