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

[Anonymous]

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 · Accepted solution]

Hi @Anonymous ,

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.

[v-veshwara-msft]

Hi @Anonymous ,
We wanted to kindly follow up regarding your query. If you need any further assistance, please reach out.
Thank you.

[v-veshwara-msft]

Hi @Anonymous ,
Just wanted to check if the responses provided were helpful. If further assistance is needed, please reach out.
Thank you.

[v-veshwara-msft]

Hi @Anonymous ,

Just checking in to see if you query is resolved and if any responses were helpful.
Otherwise, feel free to reach out for further assistance.

Thank you.

[v-veshwara-msft · Accepted solution]

Hi @Anonymous ,

Thanks for posting this in Microsoft Fabric Community.

As mentioned by @MFelix , cascading filter behavior between unrelated dimension tables isn’t supported by default due to the single-direction relationships typically used in star schema models. Since the dimension tables are not directly related, slicers from one won't impact the others.

Bi-directional relationships require more processing and so they can negatively impact on query performance, especially as the number of bi-directional relationships in the model increases. A common workaround, as already pointed out, is to use a measure that evaluates the fact table with CROSSFILTER() and then apply that measure as a visual-level filter (e.g. is not blank) on the other slicer. This allows you to simulate a cascading effect without altering the model structure.

CROSSFILTER function - DAX | Microsoft Learn

Hope this helps. Please reach out for further assistance.

Thank you.

[MFelix · Accepted solution]

Hi @Anonymous ,

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.