Cascading filters not working between dimension tables in DirectQuery model with multiple fact table

[VMariapp]

I'm working on a Power BI model using DirectQuery. In our scenario, we have multiple fact tables and independent dimension tables. These dimensions are connected to the respective fact tables, but not directly to one another.

 We expected cascading filter behavior between slicers from different dimension tables, but it is not working as expected. Here's how the setup looks:

•  Dimension tables are connected only to their corresponding fact tables.
• Dimension tables are not directly related each other.
• The model is in DirectQuery mode and page refresh is enabled.
• We want cascading slicers. For example when select from one dimension, it should filter on another slicer.

Issue:

  Cascading behavior is not happening. I believe it's due to the use of multiple fact tables or limitations in relationship/filter direction. Also, I am unable to enable bidirectional filtering in the model -- Due to DirectQuery or Ambiguity concerns.

Questions:

1. What's the best approach to enable cascading filter behavior between unrelated dimension tables across multiple fact tables in DirectQuery?
2. Are there recommended workarounds??
3. Is bidirectional filtering supported in this kind of setup?

Any guidance or examples would be appreciated. TIA

[MFelix]

Hi @VMariapp ,

From what you have written you are using a proper star schema so there is no cascading of the filters between the slicers because the relationship do not allow that, you refer that you have single filtering (the correct option) and that only allows you to filter from the dimensions to the facts, since there is no relationship between the dimension you cannot filter those tables.

What I use in a similar models is to have a measure that make the crossfiltering active something similar to:

Filter = CALCULATE(COUNTROWS(Fact), CROSSFILTER(Dimension[Column], Fact[Column], Both))

Then I use this measure in the other slicers I want to have being filtered by the specific dimension, I use it in the filter pane and set the option to is not blank. this will cause the filters to be cascading.