Forum Discussion
Resources time out on visuals
Has anyone seen similar behaviour with Power BI's VertiPaq/query engine when grouping columns from the many-side and one-side of a high-cardinality 1:* relationship?
Is there a known optimization/query-plan behaviour that would explain why the relationship traversal becomes extremely expensive in this scenario?
I'm particularly interested in whether this is:
- Expected behaviour due to the combination of grouping + relationship + measure evaluation,
- A semantic-model design limitation,
- A VertiPaq/query-engine issue,
- Or something else I should investigate.
I can provide the PBIX reproduction model, Performance Analyzer DAX query, model diagram, and exact reproduction steps if useful.
Attaching link to the power BI file
https://drive.google.com/drive/folders/1z9_2N2r3jV3PfkrysCUVLfZcNCp6G0A7?usp=sharing
Hi p_raza,
I would not assume this is a VertiPaq bug yet.
The first thing I would look at is the shape and cardinality of the grouping itself.
Microsoft’s star schema guidance recommends using dimension tables for filtering/grouping and fact tables for summarization.
So if the visual is grouping by a high-cardinality column from the many/fact side and also by a column from the related one-side dimension, the visual can end up requesting a very large number of groups before the measure is evaluated.
One particularly important check:
If the two columns are effectively the relationship key from both sides, remove the many-side version and group only by the dimension-side column.
There is normally no reporting benefit in grouping by both copies of the same key, and the fact-side grouping can greatly increase the query shape.
I would test this incrementally:
- Keep the measure but remove the many-side grouping column.
- Replace that field with the equivalent attribute/key from the one-side dimension.
- Add the fields back one at a time and watch where execution time increases.
- Capture the visual with Performance Analyzer and run the generated query in DAX Query View.
Microsoft’s DAX Query View guidance supports taking the exact visual query from Performance Analyzer, which is useful here because it lets you test the grouping independently of the visual.I would also check the number of distinct values in the many-side field.
A “resources exceeded” error does not necessarily mean the relationship is invalid. Microsoft documents that visual queries have memory and timeout limits, so a query with an extremely large grouping set can hit those limits even if the DAX itself is straightforward.
My working assumption would therefore be:
high-cardinality fact-side grouping + relationship propagation + measure evaluation across the generated groups = expensive queryrather than relationship traversal alone.
If the model is intended to follow a star schema, I would try to move the grouping attribute into the dimension and keep the fact table focused on measures/numeric detail.
If you can post the Performance Analyzer DAX query and the relevant part of the model diagram, that would make it possible to tell whether the expensive part is the grouping shape, the measure, or something more unusual in the engine plan.
AI-assisted drafting: AI was used to help structure and phrase this response. I reviewed and validated the technical content before posting.
4 Replies
- FarhanJeelani
Super User
Hi p_raza , I downloaded your PBIX and had a look. I think this is, expected behaviour. It's not a VertiPaq bug, and the relationship traversal isn't really the expensive part. The cause is the measure cap test = 1.
What's happening
The table visual sends a SUMMARIZECOLUMNS query. Two rules matter here:
- Auto-exist only works within one table. The engine only returns combinations that actually exist for group-by columns from the same table. Account Number and Customer Key both come from Customer_Account_Relationship, so they give at most ~729K existing pairs. Customer[Customer Short Name] comes from a different table, so it gets cross-joined with those pairs. The 1:* relationship doesn't limit that cross join. It only matters when a measure that touches the fact table is evaluated.
- Rows are removed only when every measure returns BLANK. After building the cross join, the engine evaluates the measures and drops the rows where they're all blank. cap test = 1 never returns BLANK, so nothing gets dropped. The engine has to produce roughly 729K × 600K combinations, which is where the time-out comes from.
That's why your test pages behave differently:
- Test A (no measure) and Test B (COUNTROWS): the measure is BLANK for combinations that don't exist, so they're removed and only real rows come back.
- Test C (cap test = 1 with Short Name from the Customer table): you get the full cartesian product, so it times out.
- Test D (cap test = 1 with the RELATED() copy of Short Name on the fact table): all grouping columns are now in one table, auto-exist applies, and it works.
Fixes (any one of these)
- Make the measure return BLANK where there's no fact data, for example:
cap test = IF ( NOT ISEMPTY ( 'Customer_Account_Relationship' ), 1 )
This applies to any measure that returns 0, a constant, a fixed text label, or + 0 to "show zeros". - Take the descriptive attributes from a single table, as you did in Test D. Or take all grouping columns from the dimension side where that makes sense.
- Make sure "Show items with no data" is off on those fields.
To confirm it, run the Performance Analyzer query in DAX Studio with Server Timings on. On Test C you should see a huge materialization or cross join (a very large row count in the storage-engine queries or the formula-engine spool). With the ISEMPTY version of the measure, it should drop to roughly the row count of the fact table.
Unrelated, but worth fixing: the model has six auto date/time LocalDateTable_* tables. Turning off Auto date/time under File > Options > Current File > Data Load will shrink the model a bit.
Please mark this post as solution if it helps you. Appreciate Kudos.
v-abhinavmuCommunity Support
Hi p_raza,
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-abhinavmu
Hi p_raza,
May I check if this issue has been resolved? If not, Please feel free to contact us if you have any further questions.
Thank you
- ShivekMaharaj
Community Champion
Hi p_raza,
I would not assume this is a VertiPaq bug yet.
The first thing I would look at is the shape and cardinality of the grouping itself.
Microsoft’s star schema guidance recommends using dimension tables for filtering/grouping and fact tables for summarization.
So if the visual is grouping by a high-cardinality column from the many/fact side and also by a column from the related one-side dimension, the visual can end up requesting a very large number of groups before the measure is evaluated.
One particularly important check:
If the two columns are effectively the relationship key from both sides, remove the many-side version and group only by the dimension-side column.
There is normally no reporting benefit in grouping by both copies of the same key, and the fact-side grouping can greatly increase the query shape.
I would test this incrementally:
- Keep the measure but remove the many-side grouping column.
- Replace that field with the equivalent attribute/key from the one-side dimension.
- Add the fields back one at a time and watch where execution time increases.
- Capture the visual with Performance Analyzer and run the generated query in DAX Query View.
Microsoft’s DAX Query View guidance supports taking the exact visual query from Performance Analyzer, which is useful here because it lets you test the grouping independently of the visual.I would also check the number of distinct values in the many-side field.
A “resources exceeded” error does not necessarily mean the relationship is invalid. Microsoft documents that visual queries have memory and timeout limits, so a query with an extremely large grouping set can hit those limits even if the DAX itself is straightforward.
My working assumption would therefore be:
high-cardinality fact-side grouping + relationship propagation + measure evaluation across the generated groups = expensive queryrather than relationship traversal alone.
If the model is intended to follow a star schema, I would try to move the grouping attribute into the dimension and keep the fact table focused on measures/numeric detail.
If you can post the Performance Analyzer DAX query and the relevant part of the model diagram, that would make it possible to tell whether the expensive part is the grouping shape, the measure, or something more unusual in the engine plan.
AI-assisted drafting: AI was used to help structure and phrase this response. I reviewed and validated the technical content before posting.
