hierarchy question

[jvandyck]

Hi

I have a hierachy in my listbar with 2 levels displaying the organization region on level 1 and the office location on level 2.

I want this listbar to filter the legend on a line chart in such a way that if a level 2 office is chosen in the listbar, the linechart shows sales for all offices in the region (level1) of that office. If a level 1 region is chosen in the listbar, the line chart should show all regions in the linechart. Keep in mind the listbar is also used to interact with other visualizations in a standard way.

Region Level 1	Office Level 2	Sales
Vlaanderen 1	Brugge	10
Vlaanderen 1	Roeselare	15
Vlaanderen 2	Hasselt	25
Vlaanderen 2	Antwerpen	30
Vlaanderen 2	Turnhout	20

So if the listbar selected value is Brugge, the chart should show Brugge and Roeselare. If Antwerpen and Hasselt are selected, the chart should show Antwerpen, Hasselt and Turnhout. If Turnhout and Roeselare are selected, the chart should show all offices in the table. If the listbar selected value is Vlaanderen 2, the chart should show Vlaanderen 1 and Vlaanderen 2.

Is something like this possible? Thanks a lot for your help!

[Anonymous]

Hi @jvandyck ,

Could you tell me if your problem has been solved? If it is, kindly Accept it as the solution. More people will benefit from it.

Best Regards,
Eyelyn Qin

[Anonymous · Accepted solution]

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