Filter/slice by column that exists in all tables

[Anonymous]

I have a scenario that I expect other people face, but I am not sure how to fix it in a simple and effective way.

I have a column (Company No) that exists in all tables, both fact tables, and dimension tables. So my data model has data for 5-6 different companies, and a specific set of master data for each company. 

In a report I want to have the option to choose if I want to see the data for just one company, or for all the companies, ideally as a filter in the filter pane. At the moment, this works great to filter the fact tables and only show me data for the selected company. But what I am after is how can the dimension tables also be filtered, so that when I choose company X, I will then see in the filter/slicer just the customers that company X has?

Here's a simplified data model

The actual data model is a lot more complex and includes more than 40 dimension tables and 10 fact tables, plus bridges where necessary. 

Solutions I have thought of so far:

• Build relationships directly between Company and dimension tables(and remove relationship directly to fact tables), but this is not possible, since it creates ambiguity in the data model, and when I filter Sales by Company, the data model is not sure if it should filter via Item or via Company, even though the end result would be the same in our case.
• Use Sync slicers in the report, where I sync the company slicer with the company no from each dimension table -> difficult to manage if I have 10 different dimensions used in the report page?

[Mariusz · Accepted solution]

Hi @Anonymous 

The below article should answer your question 
https://www.sqlbi.com/articles/syncing-slicers-in-power-bi/

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.

[Anonymous]

I've been using the chiclet slicer, that already had a similar behaviour to the one made available after the June update, so I guess I'll just wait until this behaviour also becomes available in the filter pane. 🙂 Thank you for your answer @Mariusz