cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
rundontwalk Frequent Visitor
Frequent Visitor

Filter on a value when no visual filter is applied

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. 

 

LocationProductRank
LondonA1
LondonB2
LondonC3
MadridC1
MadridB2
MadridA3
FrankfurtB1
FrankfurtC2
FrankfurtA3
ALLC1
ALLA2
ALLB3

 

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.

 

pbi_scr_cap.PNGNeed 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

1 ACCEPTED SOLUTION

Accepted Solutions
v-joesh-msft Established Member
Established Member

Re: Filter on a value when no visual filter is applied

Hi  @rundontwalk ,

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:2.PNG

 

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/EYDBexq6JqVAtGOWpR...

 

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.

View solution in original post

3 REPLIES 3
Super User
Super User

Re: Filter on a value when no visual filter is applied

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.

v-joesh-msft Established Member
Established Member

Re: Filter on a value when no visual filter is applied

Hi  @rundontwalk ,

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:2.PNG

 

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/EYDBexq6JqVAtGOWpR...

 

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.

View solution in original post

rundontwalk Frequent Visitor
Frequent Visitor

Re: Filter on a value when no visual filter is applied

Thanks, this is perfect!

Helpful resources

Announcements
Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 52 members 1,045 guests
Please welcome our newest community members: