Pivot columns to use values in one slicer but getting same filtering result as they were separate

[maneschr2022]

Hi, I would like to know if someone can help me on this one.

I have a table visual that contains some columns and there are 3 columns that are called " Checked wheels", "Checked mirrors", Checked motor"

The table visual looks like this:

Car type      Year     Brand    Checked wheels  Checked mirrors   Checked motor

Truck         2020       A                Yes                   Yes                       Yes

4WD          2011       B               No                    Yes                         No

Small         2018       C               Yes                     No                        Yes

Big            2015        D               No                    No                         No

Now I want to convert " Checked wheels", "Checked mirrors", Checked motor" as pivot them to be able to  use those 3 columns in one slicer where it displays in Hierarchy(only using 2 columns) and when you click on  each of them shows the option Yes,No in the slicer.

Example of slicer:

                    Checked wheels

                            Yes

                            No

                 Checked mirrors 

                           Yes

                           No

                Checked motor

                         Yes

                         No

At the moment that works fine for me by creating a new table and pivot those 3 columns and conect it to the main table bidireactional relationship to get this one slicer.

The only issue is that when I play with the slicer it doesn't filter as I would like. For example when I chose on the slicer No for Checked wheels", "Checked mirrors" and "Checked motor" it actually doesn't show just the No on the table visual

it should show only this row:

Car type      Year     Brand    Checked wheels  Checked mirrors   Checked motor

Big            2015        D               No                    No                         No

Instead it shows some rows with Yes as well.

But if I use the 3 separate slicers for each of the columns and I choose No on all of them I get the expected result. 

Is there a way to use only one slicer but achieve the same result as I would have 3 separate slicer to filter the values properly?

[Anonymous · Accepted solution]

Hi  @maneschr2022 ,

I created a sample pbix file(see attachment) for you, please check whether that is what you want.

1. Create a dimension table as below

2. Create a measure as below:

Flag = 
VAR _selchecked =
    ALLSELECTED ( 'Checked'[Checked] )
VAR _selcard =
    SELECTEDVALUE ( 'Table'[Card type] )
VAR _tab =
    CALCULATETABLE (
        VALUES ( 'Table'[Card type] ),
        FILTER (
            'Table',
            'Table'[Checked mirrors]
                IN _selchecked
                && 'Table'[Checked motor]
                IN _selchecked
                && 'Table'[Checked wheels] IN _selchecked
        )
    )
RETURN
    IF ( _selcard IN _tab, 1, 0 )

If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards

[amitchandak]

@maneschr2022 , Unpivot the three columns in power query and check if that can help

https://radacad.com/pivot-and-unpivot-with-power-bi