Filter on a value when no visual filter is applied

[Anonymous]

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.

Need to show ALL as default value when nothing is selected.

I've gone through a lot of help posts but haven't seen anything I can use. 

Thanks again for your attention. 

Serge

[v-joesh-msft · Accepted 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：

https://qiuyunus-my.sharepoint.com/:u:/g/personal/pbipro_qiuyunus_onmicrosoft_com/EYDBexq6JqVAtGOWpR7E_xUBxo_j7z3R67K9BrV2xVmKWQ?e=lzdCiW

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.

[Anonymous]

Thanks, this is perfect!

[d_gosbell]

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.