Forum Discussion

PC20's avatar
PC20
Frequent Visitor
3 years ago

Dynamic hierarchy based on other visual

I have 2 charts. Both charts have same hierarichal data. 
Chart 1: Matrix Visual & Chart 2: Funnel Chart
I have created a demo pbix file:   https://drive.google.com/file/d/1fEAb2ZsymWPF6DJjVRL0EPRBZWRzRapp/view?usp=sharing 

Based on the value in Chart1, Chart2 hierarchy should be updated with 1 level down of what is selected in Chart 1.
For eg:, if I select "A" in Chart 1, Chart 2 should display the data for A1 & A10.
if I select "A1" in Chart 1, Chart 2 should update to A2.
If "C2" is selected in Chart 1, Chart 2 should update to Blank & C3.
I have created Bookmarks to display this in Demo pbix file.

Need Help

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi PC20 

    If you want to dynamic change the hierarchy,  you may need to create slicers.

    e.g

    1.create a new table

    Table 2 = SUMMARIZE('Table',[L1],'Table'[L2],'Table'[L3],'Table'[L4])

    2.Then create a paramater

    3.Then create a measure and put the measure to the para slicer visual filter

    Measure = SWITCH(TRUE(),ISFILTERED('Table 2'[L3]),IF(3 IN VALUES(Parameter[Parameter Order]),1,0),ISFILTERED('Table 2'[L2]),IF(2 IN VALUES(Parameter[Parameter Order]),1,0),ISFILTERED('Table 2'[L1]),IF(1 IN VALUES(Parameter[Parameter Order]),1,0),1)

     

    3.Then create a new measure

    Measure 2 = CALCULATE(SUM('Table'[Col1]),FILTER('Table','Table'[L1] IN VALUES('Table 2'[L1])&&'Table'[L2] IN VALUES('Table 2'[L2])&&'Table'[L3] IN VALUES('Table 2'[L3])&&'Table'[L4] IN VALUES('Table 2'[L4])))

    put the paramater and the measure to the visual

     

    Output

     

    Best Regards!

    Yolo Zhu

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

     

     

    • PC20's avatar
      PC20
      Frequent Visitor
      3 years ago

      Anonymous It might not be a feasible option in my case. It is a Management chain hierarchy.
      I have 10 levels in hierarchy ,with some of the levels containing more than 1000 values.

      And client doesn't prefer the chosing the Slicer option. Ask is to make it completely dynamic.

      • Anonymous's avatar
        Anonymous
        Not applicable
        3 years ago

        Hi PC20 

        If you don't use the slicer, the function cannot implement, because the hierarchy cannot dynamically change wihtout the paramater slicer.

         

        Best Regards!

        Yolo Zhu