Creating a Binary Toggle Filter from 3 Values

[Will_MJ_Rayner]

I have a colum in a table with a three statuses for the row: "Staus 1","Status 2" and "Both". I would like to implement a binary single select filter showing only options of "Staus 1" and "Status 2" where the records for "Both" are included in the results when either 1 or 2 are selected. Essentially this needs to be a toggle between Status 1 & 2 where "Both" is included in results for either toggle.

Bookmarks are not an option here as I have multiple bookmarks already present on this tab. It has to be a slicer solution that will act on all bookmarks.

[johnt75 · Accepted solution]

You could create a helper table like

Status Slicer = SELECTCOLUMNS (
    {
        ( "Status 1", "Status 1" ),
        ( "Status 1", "Both" ),
        ( "Status 2", "Status 2" ),
        ( "Status 2", "Both" )
    },
    "Slicer status", [Value1],
    "Actual status", [Value2]
)

and then link the [Actual Status] column to the status column in your table. It would need to be many-to-many but you can make it single direction so that the slicer table filters the fact table.

[Will_MJ_Rayner]

Yes! That works perfectly!!😁👍

As it was previously with three options that users would have to select both and then one of the other two options, was causing user confusion, but this removes all of this unsurety. I've set it to single select so it's either one or the other. Perfect.

Thank you so much!