Performance Issue: DirectQuery Semantic Model in Fabric with Many-to-Many Relationship

[alexandra-pbi]

Hello community experts,

We’re currently facing a performance challenge with a semantic model in DirectQuery mode deployed in Microsoft Fabric (capacity F128). The data source is a Microsoft Warehouse.

Scenario:

• We have a fact table containing billions of transaction records.

• A card dimension table (Type 2 SCD) contains multiple versions of the same card.

• Filters are applied on both tables:

  • Fact: transaction date, type, and channel

  • Dimension: card type and product

Our business requirement is to join the fact and dimension tables on a business ID (not the surrogate key). This naturally results in a many-to-many relationship.

We’ve tried:

1. Using a many-to-many relationship directly.

2. Implementing a bridge table with bidirectional filtering.

In both cases, the generated query enumerates all business keys individually in the WHERE clause, for example:

SELECT ... 
FROM fact 
WHERE card_id IN (id1, id2, id3, ...)

This causes the query to exceed capacity limits, resulting in the following error: "Query Exceeded the Available Resources".

In some random cases, we observe that inner join is applied and this enumeration is avoided.

Question:

• How can we optimize or fine-tune this model to handle complex joins more efficiently in DirectQuery mode?

• Is there any way to enforce an inner join between the fact and dimension tables instead of Power BI enumerating keys?

• Any best practices or design patterns for handling high-volume many-to-many relationships in Fabric models?

Any insights, workarounds, or proven techniques would be greatly appreciated!

Thank you in advance for your help and expertise.

[parry2k]

@alexandra-pbi kind of snowflake schema, instead of a star schema

[parry2k]

@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. 

[alexandra-pbi]

@parry2k 

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.

[v-tejrama]

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

[v-tejrama]

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.

[v-tejrama]

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.

[parry2k]

@alexandra-pbi have you looked at aggregation for the common visualization and use DQ for drill thru or more detial information.  User-defined aggregations - Power BI | Microsoft Learn

[parry2k]

@alexandra-pbi why you cannot add a surrogate key to avoid many to many relationship?

[alexandra-pbi]

Thank you for your response!

To clarify, there is a surrogate key in both the fact and dimension tables.

However, it’s a business requirement to filter transactions based on the business key rather than the surrogate key.

Example:
If a card with Business ID = A has two versions (SK = 1000 and SK = 1001) with only a minor change in a secondary attribute, the business team wants to see all transactions for that card — both before and after the attribute change.

My main question is:

In what cases does Power BI (in DirectQuery mode) generate a query that enumerates all business keys (e.g., WHERE card_id IN (...)) instead of performing a straightforward INNER JOIN?

Understanding this behavior would help us determine how to adjust the model or relationships to achieve more efficient query folding.

Thank you again for your time!