cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
WaqarArshad Regular Visitor
Regular Visitor

Advanced power bi Filter

Hello, Suppose i have the following dataset:

ID, colour
1, Amber
2, Red
3, Amber
4, Red

 

If i create a slicer using the colour, it could return 2 options: Red and amber.

 

I would like the slicer to show the following 2 options: slice1 and slice 2 where

 

slice1 shows all All red or amber rows

slice2 shows all All red rows

 

The problem that's proving difficult is the overlap between the selection. I have thought it about leaving the select all option on but the labelling will not be clear as i believe select all can not be renamed.

 

Any help would be appreciated. Thanks

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Support Team
Community Support Team

Re: Advanced power bi Filter

@WaqarArshad 

 

You may refer to the following post.

https://community.powerbi.com/t5/Desktop/Tricky-Slicer-Options/m-p/573381#M270846

Community Support Team _ Sam Zha
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

7 REPLIES 7
Super User
Super User

Re: Advanced power bi Filter

Hi @WaqarArshad 

One quick solution would be:

1. Add  calculated column to the table:

 

 

NewColumn =
IF (
    Table1[colour] IN { "Red", "Amber" },
    "Slice 1",
    IF ( Table1[colour] = "Red", "Slice 2" )
)

2.  Use NewColumn for the slicer

 

 

 

WaqarArshad Regular Visitor
Regular Visitor

Re: Advanced power bi Filter

hi @

 

Thank you for your answer. Unfortuantely its not exactly what I am looking for. The if statement will evaluate the colours column and there are only 2 possible colours which are amber and red. As the first if statement checks for red and amber colours, it will always return true. The second if statement will never get a chance to execute as a result of that.  

 

When i drag the new column to the slicer, it will only show slice 1 in selection box. 

Super User
Super User

Re: Advanced power bi Filter

One thing I did was to add "Color is Red"  as a visual level filter on the slicer.  Then it only shows the option to filter Red items, but clicking off of it goes back to all items. You may also want to use a custom visual like the Chiclet Slicer.  

 

I'm curious, why would "Select All" be too confusing for your use case?  It seems pretty straightforward to me. It could even be combined with the visual level filter from above so that the slicer only shows "Select All" and "Red", but works as you describe.

mussaenda Senior Member
Senior Member

Re: Advanced power bi Filter

Untitled.png

 

if creating nother table is an option for you,

here's the pbix : slicer test pbix

 

I have encountered this problem also and this is how i solved it.

Hope this helps you too

WaqarArshad Regular Visitor
Regular Visitor

Re: Advanced power bi Filter

Hi @mussaenda,

 

Your solution seems to be doing what I'm after but could you potentially explain the logic and how it works?

 

Thank you

mussaenda Senior Member
Senior Member

Re: Advanced power bi Filter

Hi @WaqarArshad

 

I don't know how to explain this one. I just got the idea from this thread

as suggested by @jbarnes. Maybe he can explain you better.

 

Did this suggestion solved your issue?

Highlighted
Community Support Team
Community Support Team

Re: Advanced power bi Filter

@WaqarArshad 

 

You may refer to the following post.

https://community.powerbi.com/t5/Desktop/Tricky-Slicer-Options/m-p/573381#M270846

Community Support Team _ Sam Zha
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

Helpful resources

Announcements
New Kudos Received Badges Coming

New Kudos Received Badges Coming

Kudos to you if you earned one of these! Check your inbox for a notification.

Microsoft Implementation for Communities Wins Award

Microsoft Implementation for Communities Wins Award

Learn about the award-winning innovation that was implemented across Microsoft’s Business Applications Communities.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Kudoed Authors (Last 30 Days)
Users online (1,219)