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.
I am looking to filter reports based on a list of Staff Members chosen from a slicer. I want to show which values are selected in a card.
If one value is selected, return its value (Staff Name). If none explicitly selected, return "All Selected". If more than one, show "N Selected" where N is the number of boxes checked.
The following code works in the first 2 scenarios, but when multiple items are selected it returns "All Selected":
Slicer Selected Measure =
VAR selected = SELECTEDVALUE(Staff_Details[Staff Member])
RETURN IF (
ISBLANK(selected),
"No Staff Member Selected",
IF (
DISTINCTCOUNT(Staff_Details[Staff Member]) = 1,
selected,
CONCATENATE(DISTINCTCOUNT(Staff_Details[Staff Member]), " Staff Members Selected")
)
)
How to fix this so that it returns the values as required?
Solved! Go to Solution.
Measure =
VAR SelectedMember =
COUNTROWS ( VALUES ( Staff_Details[Staff Member] ) )
VAR All_Members =
COUNTROWS ( ALL ( Staff_Details[Staff Member] ) )
VAR Result =
SWITCH (
TRUE(),
All_Members = SelectedMember, "All Selected",
SelectedMember = 0, "No Member Selected",
SelectedMember = 1, SELECTEDVALUE ( Staff_Details[Staff Member] ),
SelectedMember > 1, SelectedMember & " Staff members Selected"
)
RETURN Result
Perfect - works a treat!
Measure =
VAR SelectedMember =
COUNTROWS ( VALUES ( Staff_Details[Staff Member] ) )
VAR All_Members =
COUNTROWS ( ALL ( Staff_Details[Staff Member] ) )
VAR Result =
SWITCH (
TRUE(),
All_Members = SelectedMember, "All Selected",
SelectedMember = 0, "No Member Selected",
SelectedMember = 1, SELECTEDVALUE ( Staff_Details[Staff Member] ),
SelectedMember > 1, SelectedMember & " Staff members Selected"
)
RETURN Result
Hi Anriksh
I've now extended this logic to multiple slicers interacting on the same table (Staff Details). Unfortunately this breaks the intended logic of the solution - if for example Staff Member John is explicitly selected, the corresponding Measure for Staff Team goes from All Selected to "1 Team Selected" because the rows in the filtered table are filtered from the whole.
What I am looking for is a measure that shows only the Slicers where a value has been explicitly chosen - even if the team is implicitly selected by the application of the Staff Member filter. Is there a way to do this that returns the results of the Checked values in the slicer itself ,as opposed to a measure based on the filtered table rows?
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 |
---|---|
47 | |
23 | |
20 | |
15 | |
13 |
User | Count |
---|---|
51 | |
42 | |
39 | |
19 | |
19 |