Forum Discussion

jvandyck's avatar
jvandyck
Icon for Helper IV rankHelper IV
5 years ago
Solved

hierarchy question

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 1Office Level 2Sales
Vlaanderen 1Brugge10
Vlaanderen 1Roeselare15
Vlaanderen 2Hasselt25
Vlaanderen 2Antwerpen30
Vlaanderen 2Turnhout20

 

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!

Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    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

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    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

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    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