power bi hierarchy select layer l-1
I'm currently working with power bi and wanted to filter a factory production site
The site, which is itself divided into zones themselves divided into lines create the hierarchy I have in power bi. ref left of the exemple
On a Pie chart, I'd like to visualize the distribution of values attributed to thoses locations but only to the next layer of the hierarchy. ref right of the exemple
Exemple:
As you can see, selecting S only shows me the next layer of values ie. Z1 and Z2
When selecting Z1 I then have the three lines (L1, L2, L3)
tests I've made so far:
Best I've found is duplicating the hierarchy table and linking the values to there parent ids. So when filtering an element, values connected to it don't show up.
I also tried different approches to hide the deep layers, but can't figure a way to do it, any ideas how to?
update 2 days after posted:
It's been 2 days since I posted and I continued doing some tests, I thought I had a solution by doing a great-parent table of the hierarchy. I would then only have to substract the great-child to childs to have the data I wanted, unfortunatly I couldn't figure it out.
I also tried creating a copy of the table, filtered while created but it doesn't seem to affect the output.
I tried doing some magic with ids but same problem, couldn't find a way to achieve the problem.
Here are my tests data:
hierarchy:
| Id | Name | parentId |
|---|---|---|
| 1 | Site | |
| 2 | Zone1 | 1 |
| 3 | Zone2 | 1 |
| 4 | Line1 | 2 |
| 5 | Line2 | 2 |
| 6 | Line3 | 3 |
| 7 | Line4 | 3 |
Sensor:
| Id | Name | hierarchyId |
|---|---|---|
| 1 | Total site | 1 |
| 2 | Total zone1 | 2 |
| 3 | Total zone2 | 3 |
| 4 | Line1 | 4 |
| 5 | Line2 | 4 |
| 6 | Line3 | 5 |
| 7 | Line4 | 6 |
mesures:
| SensorId | Name | time |
|---|---|---|
| 1 | 240 | 21/06/2022 10:21:10.000 |
| 2 | 140 | 21/06/2022 10:21:10.000 |
| 3 | 100 | 21/06/2022 10:21:10.000 |
| 4 | 70 | 21/06/2022 10:21:10.000 |
| 5 | 20 | 21/06/2022 10:21:10.000 |
| 6 | 50 | 21/06/2022 10:21:10.000 |
| 7 | 100 | 21/06/2022 10:21:10.000 |
| 1 | 260 | 21/06/2022 10:31:10.000 |
| 2 | 150 | 21/06/2022 10:31:10.000 |
| 3 | 110 | 21/06/2022 10:31:10.000 |
| 4 | 80 | 21/06/2022 10:31:10.000 |
| 5 | 20 | 21/06/2022 10:31:10.000 |
| 6 | 50 | 21/06/2022 10:31:10.000 |
| 7 | 110 | 21/06/2022 10:31:10.000 |
| 1 | 260 | 21/06/2022 10:41:10.000 |
| 2 | 160 | 21/06/2022 10:41:10.000 |
| 3 | 100 | 21/06/2022 10:41:10.000 |
| 4 | 80 | 21/06/2022 10:41:10.000 |
| 5 | 20 | 21/06/2022 10:41:10.000 |
| 6 | 60 | 21/06/2022 10:41:10.000 |
| 7 | 100 | 21/06/2022 10:41:10.000 |
The only way I solved the problem is with a semi-automated solution.
Defining each layer in calculated columns:
diging down the chart with the multiple columns as filters:
It is not fully automated but still do the job. If you have a better solution I'll be happy to see it :)