Table of locations for Slicer Drill Down

[Daniel_B]

Hi There

I have to source data tables which use the same list buildings and I want to be able to filter at different levels using slicers to drill down to the lowest levels available based on the previous selection for example 

Site 1 has 7 Departments and 8 Sub Departments

Site 2 has 10 Departments and 40 Sub Departments

If I select Site 1, then I should not be able to see any of Site 2's Departments and Sub Departments

I created individual tables and realised that meant the relationship was only at that level (Site -> Site but wasn't linked to Department)

I created a table in Excel which contains all of the Sites and Departments but it is at a row level so there are multiple versions of site 1 which doesnt' work either

What is the best way to create multi-level slicers (I don't want filters) to show the relationship between the Sites as per the above

Thanks

[v-lionel-msft]

Hi @Daniel_B ,

Are your fact table like this?

Maybe you can use hierarchical filter:

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[edhans · Accepted solution]

You should create the table at the same level of granularity as in your FACT table, or your data. Presumably that is at the department level. It wouldn't make sense if Site data was in the FACT table as you couldn't tell the difference between Site 1/Dept 1 and Site 1/Dept 2.

But if your FACT table is at the department level, then your DIMENSION table for location would be at the Department level as well and look something like this:

Department	Site
Dept 1	Site 1
Dept 2	Site 1
Dept 3	Site 1
Dept 4	Site 2
Dept 5	Site 2
Dept 6	Site 2

Then you relate Location[Department] to the department in your FACT table as a One-To-Many, and will be able to slice by department or site.