Forum Discussion
Power Bi hierarchy from 2 different tables
How to define a hierarchy in slicer from 2 different tables where we have Unique records in master table and relationship from these table to main table columns.
Say for eg Main table has Personnel has column Employee,Country,State
Master Tables Country with column Country Name,Country Code
& State with column State Code,State Name
Both Master tables have relationship to Personnel by Column country & State
I need a slicer having hierarchy of Country,State from 2 Master 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
9 Replies
- Jihwan_Kim
Super User
Hi, Minu_Latha
Please correct me if I wrongly understood.
You know your data model much better than me, and what I have understood is that I like to suggest deleting two Country and State tables, and create one table that comes from Main Table. this new table has to contain Country name / Country code / State name / State code.
In this case, I believe you can create a hierarchy-type slicer.
If you can share your sample pbix file, I can try to look into it and come up with a desirable solution.
Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Then The Master table model will be
Country Name State Name
A B
A C
A D
Then how will we link this to main table since country not unique(Relationship)
- Jihwan_Kim
Hi,
I think the State Name is unique.
Can I try to connect between StateName/Mastertable and StateName/Maintable ?
- technolog
Jihwan_Kim Could you dublicate your screenshots because of they are appeared.
