Forum Discussion
Create slicer for Hierarchy across multiple tables
Hello,
I have PowerBI with multiple tables:
Users, Projects, Protocols.
All of these tables have values that represent organizational structure (stored as separate columns). Each record contains information about:
Department, SubDepartment, and Unit.
I want to create a hierarchical slicer in PowerBI that can filter across all tables based on the organizational structure.
I've tried creating a relationship between the tables, but PowerBI only allows one relationship. But I need to create multiple connections (Department-Department, Unit-Unit in every table)
Also I don't want to create "merged" tables because data in all tables are really different. The only connection is that they all contain information about Org structure
How can I achieve this?
Thank in advance!
Actually answer from amitchandak gave me good hint and I found a solution
What I did:
1. I created new table that grab only unique values for Organisational structure
2. In all tables I created columns with aggregated values as amitchandak suggested (Key = [Department] & "-" & [Unit])
3. I created one-to-many relations for all tables with newly created table using "aggregated" field
After I plan to use not aggregated values in Slicer, that will give me hierarchy, but will filter all tables
3 Replies
- amitchandak
Super User
nikolai_nopost , if that is two columns join, create a combined column and join.
example
Key = [Department] & "-" & [Unit]
in case of direct query, you can use combinevalues to do so
Thank you!
Unfortunately I should have hierarchy slicer, when Organsational structure is kept like: Department => SubDepartment => Unit
And not a flat Slicer
Actually answer from amitchandak gave me good hint and I found a solution
What I did:
1. I created new table that grab only unique values for Organisational structure
2. In all tables I created columns with aggregated values as amitchandak suggested (Key = [Department] & "-" & [Unit])
3. I created one-to-many relations for all tables with newly created table using "aggregated" field
After I plan to use not aggregated values in Slicer, that will give me hierarchy, but will filter all tables
