Creating a slicer for multiple Yes/No columns with a hierarchy

[AlexplainLater]

Hi there,

I am new to PowerBi and I have been asked to create a slicer which looks across multiple Yes/No column.

If a column has a 'Yes' value in it, the subsequent phases will also be 'Yes'. See example below with a count at the bottom

Product	Phase 1	Phase 2	Phase 3	Phase 4	Earliest Phase
Apples	Yes	Yes	Yes	Yes	Phase 1
Bananas	No	Yes	Yes	Yes	Phase 2
Pears	No	No	Yes	Yes	Phase 3
Oranges	No	No	No	Yes	Phase 4
Kiwis	Yes	Yes	Yes	Yes	Phase 1
Avacados	No	No	Yes	Yes	Phase 3
Lemons	No	Yes	Yes	Yes	Phase 2
Melons	No	No	No	Yes	Phase 4
Limes	Yes	Yes	Yes	Yes	Phase 1
Guavas	No	No	Yes	Yes	Phase 3
COUNT	3	5	8	10

At the moment, if I select Phase 2 on the slicer, it will only show me 2 values, but instead I would like to see 5 values (all the ones in Phase 2 and the ones in Phase 1). I've tried making a CSV column (Phase 1, Phase 2) but that looks wrong on the report.

Can anyone help? Please forgive my limited knowledge in this area - I'm looking for an explaination as to how this can be achieved

[Anonymous]

Thanks for the reply from rohit1991, please allow me to provide another insight.

Hi @AlexplainLater ,

Please refers to the following steps.

Add an index column in the Power Query editor to add a unique identifier for each row.
This way, when this index column and other columns are placed in the table visual, the other columns data will not be grouped.

Preventing automatic grouping in Power BI Desktop

A field parameter table was created using the Phase 1 and Phase 2 fields. And a slicer was created using the field parameter.

Create the following measure to filter values.

Measure = 
VAR selectedPhase = IF(ISCROSSFILTERED(Parameter[Parameter]),MAX('Parameter'[Parameter]),BLANK())
RETURN
IF(selectedPhase ="Phase 2",
    IF(MAX('Table'[Phase 1]) ="Yes" || MAX('Table'[Phase 2]) = "Yes",1,0),1
    )

Drag the index columns, field parameter into the table visual. The measure is placed in the filter of the visual and set to is 1.

This way, when you select Phase 2 in the slicer, the desired 5 values are displayed.

If you need to hide the Index column, then you need to turn off the text wrap settings for the column headers and values, and then drag the Index column to hide it.

Please see the attached pbix for reference.

Best Regards,
Dengliang Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[rohit1991 · Accepted solution]

Hi @AlexplainLater ,

To create a hierarchical slicer in Power BI that filters multiple Yes/No columns while ensuring that selecting a phase includes all previous phases, you need to unpivot the phase columns and apply a custom filtering logic. First, in Power Query, unpivot the Phase 1 to Phase 4 columns so that each row represents a Product-Phase combination with a Yes/No status. Next, create a calculated column to determine the earliest phase where each product has a "Yes" value.

Then, build a Phase Hierarchy table with a corresponding Phase Order to control the filtering logic, ensuring that selecting a later phase also includes the earlier ones. Finally, create a DAX measure that filters products where their earliest phase is less than or equal to the selected phase and apply it as a visual-level filter on the table. This setup allows users to select a phase in the slicer, ensuring that all products from the selected and earlier phases remain visible, effectively implementing the desired drill-down behavior in Power BI.