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
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 Support
Hi 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.Hi alexandra-pbi ,
I wanted to follow up and see if you had a chance to review the information shared. If you have any further questions or need additional assistance, feel free to reach out.
Thank you.