Forum Discussion
Create Slicer that removes a filter
I have field "STATUS" and I need my report page to include by default all records except where value of STATUS is "bad". But I need to provide a way for users to be able to click something to include "bad" (slicer, button, etc; the type of visual isn't important). I know how to create a slicer for "Exclude (value)" but I can't for the life of me figure out how to do it the other way around. Can anyone help?
Example:
Default showing in report:
After toggle is selected:
I kicked a game-winning goal, then you moved the goal posts on me 🙂
I thought about this suggestion first, but rejected it for Bookmarks, thinking the latter would be easier.
Do you have a dimension table for Status? If not, create one. Then in there, create a CALCULATED COLUMN that is something like this:
Is Bad = IF ( [Status] = "Bad", "Yes", "No" )
and use that in the slicer. Maybe even create a two-field slicer with this one first.
4 Replies
- ToddChitt
Super User
I kicked a game-winning goal, then you moved the goal posts on me 🙂
I thought about this suggestion first, but rejected it for Bookmarks, thinking the latter would be easier.
Do you have a dimension table for Status? If not, create one. Then in there, create a CALCULATED COLUMN that is something like this:
Is Bad = IF ( [Status] = "Bad", "Yes", "No" )
and use that in the slicer. Maybe even create a two-field slicer with this one first.
I think the two-field slicer is the way I'm going to have to go with this one. Thank you for the help, and sorry about moving the goal posts on you!
- ToddChitt
I suggest you investigate Bookmarks in Power BI Desktop. And also learn how to activate them via a Button. With a Bookmark, you can first set the value(s) of your various slicer(s) then 'put a stake in the ground' with the Bookmark. With a button activating said Bookmark, users can get to a consistent state every single time with one click.
This is a good solution based on the information I shared, but there are details that I didn't include that make it not quite work. There is an existing slicer on the page for "Select User", and when I save a bookmark it also saves the selected user since bookmarks capture the state of the entire page. I need a way for my users to make a selection and toggle back and forth between "all except bad" and "all including bad".
