Forum Discussion
Performance Issue: DirectQuery Semantic Model in Fabric with Many-to-Many Relationship
Hi alexandra-pbi ,
Thanks for clarifying. The relationship setup you described, with a distinct business ID table above the SCD dimension and fact table, doesn’t change query behavior by itself. Power BI will only use that table if it’s the actual source of the filter context. If filters come from fields in the SCD dimension, like card type or product, the engine still sees multiple rows per business ID, which leads to an ambiguous filter path. In those cases, Power BI defaults to enumerating values instead of using an inner join, even if a distinct ID table is present.
This explains why your test showed the same results. Unless filters pass through the distinct ID table or the model ensures a single, clear row per business ID before reaching the fact table, Power BI will keep generating large IN lists. The bridge table doesn’t automatically redirect those filters or resolve the ambiguity from the Type 2 dimension.
To consistently achieve inner join pushdown, the model usually needs either a mapping table that links business IDs to the surrogate keys in the fact table or a version of the card dimension where each business ID is unique. These options provide a straightforward filter path and prevent the IN clause expansion.
Best Regards,
Tejaswi.
Community Support
alexandra-pbi why not create a table with unique business ids, link to dim table, one to many, and then surrogate key to fact table, one to many. Use filter from new business ids table and I trust this will get you inner join.
Thank you for the suggestion!
If you mean adding a bridge table with distinct business IDs between the dimension and the fact, I’ve already tested that approach — unfortunately, it didn’t solve the issue.
Just to confirm, are you suggesting this relationship flow?
Distinct Business IDs (1) → (*) Dim Card (1) → (*) Fact
If that’s the case, it doesn’t sound like it would achieve the desired behavior, since if no fields from the “Distinct IDs” table are used in the report, the filters would still propagate through the surrogate key, not the business key.
Could you please elaborate a bit more on this setup?
Thank you again.
Hi alexandra-pbi ,
Thanks for clarifying. The relationship setup you described, with a distinct business ID table above the SCD dimension and fact table, doesn’t change query behavior by itself. Power BI will only use that table if it’s the actual source of the filter context. If filters come from fields in the SCD dimension, like card type or product, the engine still sees multiple rows per business ID, which leads to an ambiguous filter path. In those cases, Power BI defaults to enumerating values instead of using an inner join, even if a distinct ID table is present.
This explains why your test showed the same results. Unless filters pass through the distinct ID table or the model ensures a single, clear row per business ID before reaching the fact table, Power BI will keep generating large IN lists. The bridge table doesn’t automatically redirect those filters or resolve the ambiguity from the Type 2 dimension.
To consistently achieve inner join pushdown, the model usually needs either a mapping table that links business IDs to the surrogate keys in the fact table or a version of the card dimension where each business ID is unique. These options provide a straightforward filter path and prevent the IN clause expansion.
Best Regards,
Tejaswi.
Community SupportHi alexandra-pbi ,
I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions.
Thank you.
