Create single slicer for multiple similar columns in Power BI

Question

In Power BI I have an inventory with multiple possible owners for each product.

Example data:

Product	Primary Owner	Backup Owner
Widget 1	Frank	Sally
Widget 2	Sally	John
Widget 3	John
Widget 4	Frank	Anna

Desired result: to display the full inventory in a table, and provide a slicer that users can filter the table with. The slicer would be a list of all owners (both primary and backup), and when a user is selected the table would display any row where that user is present (whether they are present in the Primary Owner or Backup Owner field.

With the above example, if you were to filter by John you would see Widget 2 and Widget 3, whereas filtering by Sally would show you Widget 1 and Widget 2.

Failed attempts: Using two distinct slicers does not work for this, as it will hide data in the other column. If John were to filter to himself as Primary Owner, he would then no longer see data for Widget 2 where he is the Secondary Owner. Concatenating the Primary and Secondary together into a joined column also does not work, because I would get items like Frank|Sally or Sally|John and the combined data does not make sense for the user as an option in the Slicer.

Finally I tried creating a separate table that contains the combined list of all Primary and Secondary Owners into a single column, then relating it to the main table, however I cannot have two active relationships at once. I know Measures have access to inactive relationships through Calculate, but I don't know how (or if it is even possible) to create a slicer from that.

Marcus Marcus · Accepted Answer · 2021-08-10T06:27:27

Edit: After re-reading your post, it would probably make sense to calculate a new table to handle this. The table will have two columns, Product and Person (or Owner in your case):

Slicer Table = 
UNION (
    SELECTCOLUMNS (
        FILTER( 'Table' , 'Table'[Primary Owner] <> BLANK() ) , 
        "Product" , 'Table'[Product] , 
        "Person" , 'Table'[Primary Owner]
        ),
    SELECTCOLUMNS (
        FILTER ( 'Table' , 'Table'[Backup Owner] <> BLANK() ) ,
        "Product" , 'Table'[Product] , 
        "Person" , 'Table'[Backup Owner]
        )
)

After this has been calculated, create a relationship between these tables, between the Product columns. Since this is purely for filtering the table on the person of interest, make it be Many-to-Many and have Slicer Table filter Table:

Create a slicer with the Person column from the Slicer Table and test the functionality:

Create single slicer for multiple similar columns in Power BI

1 Answers