Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
Minu_Latha
Frequent Visitor

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

1 ACCEPTED SOLUTION

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

Picture1.png

 

2. reference the origin table -> remain only three columns create dim-table 

Picture2.png

 

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.

Picture3.png

4. create index column -> each line is unique.

Picture4.png

5. reference the original table and rename it to Employees.  Then make original table to disable upload.

Picture5.png

In the Employees table, bring the index number from the dim-table by using merge. Then delete group1 / group2 / group3 columns.

Picture6.png

Load two tables and create the relationship like below.

Picture7.png

 

from here, the below described hierarchy-type-slicer can be easily created. 

Picture8.png

 

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 

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Go to My LinkedIn Page


View solution in original post

9 REPLIES 9
technolog
Super User
Super User

@Jihwan_Kim Could you dublicate your screenshots because of they are appeared.

Jihwan_Kim
Super User
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!!

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Go to My LinkedIn Page


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)

Hi, 

I think the State Name is unique.

Can I try to connect between StateName/Mastertable and StateName/Maintable ?

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Go to My LinkedIn Page


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 

@Minu_Latha 

 

You can just drag the fields from your table directly into a slicer to get the hierarchy:

multi slicer.JPG





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






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?

 

 

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Go to My LinkedIn Page


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

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

Picture1.png

 

2. reference the origin table -> remain only three columns create dim-table 

Picture2.png

 

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.

Picture3.png

4. create index column -> each line is unique.

Picture4.png

5. reference the original table and rename it to Employees.  Then make original table to disable upload.

Picture5.png

In the Employees table, bring the index number from the dim-table by using merge. Then delete group1 / group2 / group3 columns.

Picture6.png

Load two tables and create the relationship like below.

Picture7.png

 

from here, the below described hierarchy-type-slicer can be easily created. 

Picture8.png

 

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 

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Go to My LinkedIn Page


Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.