Forum Discussion
Power Bi hierarchy from 2 different tables
Hi, Minu_Latha
Thank you very much for your information.
I have a few times faced this types of situation, for instance, skill levels under different skill sets.
I saw some people using custom-visualizations for a slicer, but what I normally do is,1. Import source data to Power Query Editor
2. reference the origin table -> remain only three columns create dim-table
3. select all three columns and remove duplicates. In this case, there was no C group1 in the original file. However, when the original file is updated, then the dim-table will be also updated. Because it is not duplicated, but it is referenced.
4. create index column -> each line is unique.
5. reference the original table and rename it to Employees. Then make original table to disable upload.
In the Employees table, bring the index number from the dim-table by using merge. Then delete group1 / group2 / group3 columns.
Load two tables and create the relationship like below.
from here, the below described hierarchy-type-slicer can be easily created.
the link to my sample pbix file is down below.
It is not a very fancy way to solve it, but it works. I hope this helps.
https://www.dropbox.com/s/1ss7rp0901npsx9/Minu_Latha%202021%200323.pbix?dl=0
Super UserHi, Minu_Latha
Thank you for your questions.
May I ask,
In the master table,
- AAA is under AA, and at the same time under CC as well?
- BBB is under AA, and at the same time under BB as well?
- CCC is under BB, and at the same time under CC as well?
Thanks for you Spontaneous response.
Yes your understanding is right,
The hierachy level for Group1-First Level,Group2-Second Level,Group3-Third level is
A
AA
AAA
BBB
B
BB
BBB
CCC
C
CC
CCC
AAA
- Jihwan_Kim
Hi, Minu_Latha
Thank you very much for your information.
I have a few times faced this types of situation, for instance, skill levels under different skill sets.
I saw some people using custom-visualizations for a slicer, but what I normally do is,1. Import source data to Power Query Editor
2. reference the origin table -> remain only three columns create dim-table
3. select all three columns and remove duplicates. In this case, there was no C group1 in the original file. However, when the original file is updated, then the dim-table will be also updated. Because it is not duplicated, but it is referenced.
4. create index column -> each line is unique.
5. reference the original table and rename it to Employees. Then make original table to disable upload.
In the Employees table, bring the index number from the dim-table by using merge. Then delete group1 / group2 / group3 columns.
Load two tables and create the relationship like below.
from here, the below described hierarchy-type-slicer can be easily created.
the link to my sample pbix file is down below.
It is not a very fancy way to solve it, but it works. I hope this helps.
https://www.dropbox.com/s/1ss7rp0901npsx9/Minu_Latha%202021%200323.pbix?dl=0