Slicer depencies and ambiguity

[FilipK]

Assume having the following configuration

With that model in place I created a report with several slicers to filter my visuals.

• On the slicers I've got various fields from table A, D and E.
• One slicer is acting as a date slicer and is linked to table E.
• Table A and D have got a date but are not linked via date field.
• Only D is directly filtered via calendar table E.

Problem

Since table A and D are not timewise linked changes in the date slicer sometimes don't affect the other slicers that are not linked to table A

I can create also link between table E and A, but this cannot be active without creating ambiguities. 

Question

Have you got a hind for me how to realize that all slicers are affected via the data slicer?

[Anonymous]

Yeah... I'd say right off the bat: CHANGE THE MODEL. Go for a simple star-schema and your troubles will be gone in no time.

[FilipK · Accepted solution]

@Anonymous . Thanks for your response. I know, there a multiple fact tables in and it's not ideal. Changing the datamodel is not so easy, since we're using incremental refresh and not every raw date table in sql has got a date field. 

But I figured out is, that it works when I change the direction between A and F to both.

[Anonymous]

@FilipK 

The only problem with your design is that the model has too many two-way filters. This not only slows down the DAX but makes the model very dangerous. It will bite you sooner than you think when you start adding to it. If you postpone proper work on the model today, you will do it with a worse model in the future. I've been there and seen it 🙂 By the way, many fact tables is not a problem at all. The real problem is: 1) many two-way filters and 2) many dodgy connections between dimension tables themselves and between fact tables themselves. And be aware of the fact that there are numerous cases where a model is ambiguous and the engine will not let you know about it.

Also, I can't see a problem with refreshes you're talking about. Power Query lets you shape the tables the way you want and can isolate the phases: loading base tables and creating dimensions and facts.

But, of course, you'll bear the consequences of not doing the groundwork up-front 🙂