Forum Discussion
Cross FIlter issue in SSAS version 2019
I have a data model with Fact Table 1 and Fact Table 2, and I need to establish a many-to-many relationship where Fact Table 1 filters Fact Table 2. I'm using SSAS version 2019 with compatibility level 1500. However, I'm unable to use the one-way left-to-right filter argument in my DAX cross filter. When I introduce a bridge table to manage this scenario, Fact Table 1 does not filter the bridge table and instead uses bidirectional filtering. How can I resolve this issue?
Hi Anonymous
Thanks for the suggestion! The approach shared by Anonymous seems to be quite efficient.Additionally few points are are below
In SSAS 2019 (compatibility level 1500), the CROSSFILTER function does not support one-way filtering (left-to-right only) like it does in Power BI models. The CROSSFILTER direction must be either 'Both' or 'None'—OneWay is not a valid argument in SSAS Tabular models as of that version.Here’s how you can handle this scenario:
-
Use a bridge table between Fact Table 1 and Fact Table 2, and establish one-to-many relationships from each fact table to the bridge table.
-
Write a DAX measure that explicitly filters Fact Table 2 based on the selections in Fact Table 1 via the bridge.
Filtered Measure =
CALCULATE(
[Some Measure from Fact2],
TREATAS(VALUES(Fact1[Key]), BridgeTable[Key])
)This way, even though the relationship isn't enforcing direction automatically, you simulate one-way filtering usin
TREATAS.
If you're trying to achieve this behavior inside a calculated column or calculated table, then you'll need to redesign your logic to work within the row context or use measures instead.
Best Regards,
Cheri Srikanth-
4 Replies
- v-csrikanth
Community Support
Hi Anonymous
Thanks for the suggestion! The approach shared by Anonymous seems to be quite efficient.Additionally few points are are below
In SSAS 2019 (compatibility level 1500), the CROSSFILTER function does not support one-way filtering (left-to-right only) like it does in Power BI models. The CROSSFILTER direction must be either 'Both' or 'None'—OneWay is not a valid argument in SSAS Tabular models as of that version.Here’s how you can handle this scenario:
-
Use a bridge table between Fact Table 1 and Fact Table 2, and establish one-to-many relationships from each fact table to the bridge table.
-
Write a DAX measure that explicitly filters Fact Table 2 based on the selections in Fact Table 1 via the bridge.
Filtered Measure =
CALCULATE(
[Some Measure from Fact2],
TREATAS(VALUES(Fact1[Key]), BridgeTable[Key])
)This way, even though the relationship isn't enforcing direction automatically, you simulate one-way filtering usin
TREATAS.
If you're trying to achieve this behavior inside a calculated column or calculated table, then you'll need to redesign your logic to work within the row context or use measures instead.
Best Regards,
Cheri Srikanth -
- v-csrikanth
Hi Anonymous
We haven't heard from you since last response and just wanted to check whether the solution provided has worked for you. If yes, please accept as solution to help others benefit. If not, feel free to reach out.
Thank you. - v-csrikanth
Hi Anonymous
Please let us know if you need any further assistance.
Looking forward to your update.
Please Accept as Solution to help others benefit. If not, feel free to reach out.
Thank you.