Forum Discussion
Hierarchy and Show Different Levels
Reaching out for help. I have created the Hierarchy for three levels locations.
Right now, I want to the locations (whether which level drill I selected) to show the value in column ‘name' as the snapshot showed.
One way I used is: with Power Query - Copy a new column of the parent id - change one by one to the name I needed.
Any other easier ways?
Hi Anonymous ,
Thanks for reaching out to Community Forum.
I was able to reproduce this using a simple sample model (attached .pbix). The issue is not related to Power Query but to how Power BI evaluates hierarchy context in visuals. Instead of duplicating or hard coding values in Power Query, this can be handled dynamically using DAX. I created a standard hierarchy Level1Name -> Level2Name -> Level3Name and used an ISINSCOPE based measure to detect the active drill level and return the appropriate name automatically. With this approach, Level 1 shows the Level1 name, Level 2 shows the Level2 name, and Level 3 shows the Level3 name, while fully preserving drill down behavior.
The below attached .pbix demonstrates the correct output at each drill level.
Thank you.
5 Replies
- alish_b
Super User
Hey Anonymous ,
I am not sure if I can comprehend the question here. I can see that multiple parent_id can have the same 'name'. If you want the same 'name' to appear in each drill-down, then you could simply use a chart without drill down and just use the 'name' column in the field name as it would just group the counts under the name? As for the Power Query part, can you share an example of the before/after sample of the Power Query transformation?
- danextian
Hi Anonymous
Can you pelase expound further? What do you mean by relative value? Is that the value of the bars? Can you please provide a workable sample data (not an image and with confidential data removed), your expected result from the same sample data and your reasoning behind. You may post a link to Excel or a sanitized copy of your PBIX stored in the cloud.
Hi Anonymous ,
Thanks for reaching out to Community Forum.
I was able to reproduce this using a simple sample model (attached .pbix). The issue is not related to Power Query but to how Power BI evaluates hierarchy context in visuals. Instead of duplicating or hard coding values in Power Query, this can be handled dynamically using DAX. I created a standard hierarchy Level1Name -> Level2Name -> Level3Name and used an ISINSCOPE based measure to detect the active drill level and return the appropriate name automatically. With this approach, Level 1 shows the Level1 name, Level 2 shows the Level2 name, and Level 3 shows the Level3 name, while fully preserving drill down behavior.
The below attached .pbix demonstrates the correct output at each drill level.
Thank you.
