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.
Dear Community,
Thanks for looking at this. This issue has been a bear for me even though it seems like an easy use case.
I have the following table-- it captures the ranking of three products across several locations.
Location | Product | Rank |
London | A | 1 |
London | B | 2 |
London | C | 3 |
Madrid | C | 1 |
Madrid | B | 2 |
Madrid | A | 3 |
Frankfurt | B | 1 |
Frankfurt | C | 2 |
Frankfurt | A | 3 |
ALL | C | 1 |
ALL | A | 2 |
ALL | B | 3 |
The ALL rows captures overall ranks but the values are not obtained from the ranks for other locations (values for ALL are not a function of other rows).
I have a slicer and a table that filters it. I want to show the ALL location when the slicer is not selected. That is, I want to exclude the ALL values from slicer and when the user has not selected any location, I want to show the ALL values. Conversely, when a location is selected I want to show the values for that location only.
I've gone through a lot of help posts but haven't seen anything I can use.
Thanks again for your attention.
Serge
Solved! Go to Solution.
Hi @Anonymous ,
I think you can create a measure like the following:
defaultselection 1 =
IF (
COUNTROWS ( DISTINCT ( ALLSELECTED ( 'Table'[Location] ) ) )
< COUNTROWS ( DISTINCT ( ALL ( 'Table'[Location] ) ) ),
SUM ( 'Table'[Rank] ),
IF (
SELECTEDVALUE ( 'Table'[Location] ) = "ALL",
SUM ( 'Table'[Rank] ),
BLANK ()
)
)
Replace the Rank field with measure “defaultselection 1” and deselect "All" in the Filter. The following is the result:
If you need more details, you can watch the video, set default value of slicer power bi
Here is a demo, please try it:
Best Regards,
Community Support Team _ Joey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Anonymous ,
I think you can create a measure like the following:
defaultselection 1 =
IF (
COUNTROWS ( DISTINCT ( ALLSELECTED ( 'Table'[Location] ) ) )
< COUNTROWS ( DISTINCT ( ALL ( 'Table'[Location] ) ) ),
SUM ( 'Table'[Rank] ),
IF (
SELECTEDVALUE ( 'Table'[Location] ) = "ALL",
SUM ( 'Table'[Rank] ),
BLANK ()
)
)
Replace the Rank field with measure “defaultselection 1” and deselect "All" in the Filter. The following is the result:
If you need more details, you can watch the video, set default value of slicer power bi
Here is a demo, please try it:
Best Regards,
Community Support Team _ Joey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thanks, this is perfect!
You could do something like the following
Rank Measure =
VAR _location = SELECTEDVALUE('Table'[Location], "ALL") RETURN IF ( COUNTROWS(CALCULATETABLE('Table', 'Table'[Location]= _location)) = 1,
CALCULATE(MAX('Table'[Rank]),'Table'[Location] = _location))
The first variable will get the selected location from your slicer, if nothing is selected it will return "ALL"
In the return statement I am checking if there is only 1 row in the current context for 'Table'. This is basically checking if the product column has been used in the query or in a filter. If not, the expression will return blank. Finally the MAX is just to force the expression to return a scalar value. At that point we should only have 1 row anyway.
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 |
---|---|
112 | |
97 | |
82 | |
67 | |
61 |
User | Count |
---|---|
150 | |
120 | |
99 | |
87 | |
68 |