Power Bi hierarchy from 2 different tables

[Minu_Latha]

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

[technolog]

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

[Jihwan_Kim]

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!!

[Minu_Latha]

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 ?

[Minu_Latha]

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 

[PaulDBrown]

@Minu_Latha 

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?

[Minu_Latha]

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 · 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

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