Search code examples
powerbiselectionhierarchy

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:

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
Solution

  • 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 :)