hierarchy question

Hi jvandyck ,

1. Based on level1:

• Create a new table for slicer:

Level1Slicer =
DISTINCT ( SELECTCOLUMNS ( 'Table', "Level 1", [Region Level 1] ) )

• Add a new column to specify the grage in Region Level 1 column

Vla =
RIGHT ( [Region Level 1], 1 )

• Using the following formula to create measure and apply it to filter pane like this:

level 1 =
VAR _sele =
    RIGHT ( SELECTEDVALUE ( Level1Slicer[Level 1] ), 1 )
RETURN
    IF ( _sele >= MAX ( 'Table'[Vla] ), 1, 0 )

2. Based on level2 (almost the same as 1)

• Create a new table for slicer:

Level2Slicer =
SELECTCOLUMNS ( 'Table', "Level 2", [Office Level 2] )

• Using the following formula to create measure and apply it to filter pane.

level 2 =
VAR _sele =
    ALLSELECTED ( Level2Slicer[Level 2] )
VAR _v =
    SUMMARIZE (
        FILTER ( ALL ( 'Table' ), 'Table'[Office Level 2] IN _sele ),
        'Table'[Region Level 1]
    )
RETURN
    IF (
        ISFILTERED ( Level2Slicer[Level 2] )
            && MAX ( 'Table'[Region Level 1] ) IN _v,
        1,
        0
    )

Here is the pbix file.

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

Best Regards,
Eyelyn Qin