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,
I think the State Name is unique.
Can I try to connect between StateName/Mastertable and StateName/Maintable ?
Actually my Data model is like this
Master Table Group 1
NAME
A
B
C
Master Table Group 2
Name
AA
BB
CC
Master Table Group 3
NAME
AAA
BBB
CCC
Linking Table between all three master table(Junction Table)
Group 1 Group2 Group 3
A AA AAA
A AA BBB
B BB BBB
B BB CCC
C CC CCC
C CC AAA
Main Table(Employee)
Employee Name Group1 Group2 Group3 Year
S A AA AAA 2010
P A AA BBB 2010
R A AA AAA 2010
T B BB BBB 2011
Q B BB CCC 2011
D B BB BBB 2011
How should I link all the tables to get a slicer with hierarchy BASED ON Junction table
Group1,Group2,Group3
A
AA
AAA
BBB
B
BB
BBB
CCC
C
CC
CCC
AAA
And this hierarchy slicer must filter the main table(Employee)
How should I change the relationships.Can you help
PaulDBrownCommunity Champion
You can just drag the fields from your table directly into a slicer to get the hierarchy:
- Jihwan_Kim
Hi, 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