Forum Discussion
Regarding Data Load issue
Hi everyone,
I’m currently working with a live connection model that includes over 40 tables, with the storage mode set to DirectQuery. In my report, I need to display approximately 15 measures in a matrix visual. However, when attempting to load this data, I encounter an error (as shown in the screenshot).
Could you please advise if it is feasible to handle such a volume of data in a matrix when using DirectQuery? I’ve noticed that the system suggests avoiding this approach and recommends alternative storage methods instead.
Any guidance on best practices, potential optimizations, or recommended approaches to handle this scenario would be greatly appreciated.
Thanks in advance for your support.
Kind regards,
Thirumala
Hi Thirumala,
Thanks for reaching out.
"The resultset…has exceeded the maximum allowed size of '1,000,000' rows" is the 1-million-row intermediate limit.Key point: it's not the 15 measures. Aggregations like SUM/COUNT get pushed to the source and return small results. The error means something in the visual makes Power BI pull a pre-aggregation result set of over 1M rows — usually:
- A high-cardinality field on rows/columns (an ID, order number, timestamp) being grouped.
- A measure that needs all distinct values of a high-cardinality / degenerate-dimension column (stored at line grain).
- Measure or Top N filters, which fetch all categories from the source before filtering.
the fix is to reduce the rows returned:
- Find the culprit first. Performance Analyzer → copy the visual's DAX → paste into DAX Studio and check the generated SQL. It shows exactly which column forces the large result. Don't optimise blind.
- Remove or raise the grain of that high-cardinality field (group by month/category, not line-level).
- Filter early so the intermediate set stays under 1M.
- Aggregation table (Import mode) holding just that column's distinct values, if a measure genuinely needs it — facts stay DirectQuery.
References:
https://learn.microsoft.com/en-us/power-bi/connect-data/desktop-use-directquery
https://learn.microsoft.com/en-us/power-bi/guidance/directquery-model-guidanceBest regards,
Parchitect · Solutions Architect · Microsoft Fabric Specialist
4 Replies
Hi thiru2010
Direct Query has a hard limit of 1M rows per query, so a matrix with 40 tables and 15 measures is too heavy.
Best practices are reducing dimensions in the matrix. Aggregating data in the source. Using Import or Composite models for large visuals. Splitting measures across multiple visuals. Applying slicers and filters to cut the row counts.For your reference:
https://community.fabric.microsoft.com/t5/Service/Direct-Query-1-million-row-limitation-in-Power-BI-Premium/m-p/847202Hi Thirumala,
Thanks for reaching out.
"The resultset…has exceeded the maximum allowed size of '1,000,000' rows" is the 1-million-row intermediate limit.Key point: it's not the 15 measures. Aggregations like SUM/COUNT get pushed to the source and return small results. The error means something in the visual makes Power BI pull a pre-aggregation result set of over 1M rows — usually:
- A high-cardinality field on rows/columns (an ID, order number, timestamp) being grouped.
- A measure that needs all distinct values of a high-cardinality / degenerate-dimension column (stored at line grain).
- Measure or Top N filters, which fetch all categories from the source before filtering.
the fix is to reduce the rows returned:
- Find the culprit first. Performance Analyzer → copy the visual's DAX → paste into DAX Studio and check the generated SQL. It shows exactly which column forces the large result. Don't optimise blind.
- Remove or raise the grain of that high-cardinality field (group by month/category, not line-level).
- Filter early so the intermediate set stays under 1M.
- Aggregation table (Import mode) holding just that column's distinct values, if a measure genuinely needs it — facts stay DirectQuery.
References:
https://learn.microsoft.com/en-us/power-bi/connect-data/desktop-use-directquery
https://learn.microsoft.com/en-us/power-bi/guidance/directquery-model-guidanceBest regards,
Parchitect · Solutions Architect · Microsoft Fabric SpecialistHi thiru2010,
Thank you for reaching out to the Microsoft Fabric Community Forum. Also, thanks to krishnakanth240, Parchitect, for those inputs on this thread.Has your issue been resolved? If the response provided by the community member krishnakanth240, Parchitect, addressed your query, could you please confirm? It helps us ensure that the solutions provided are effective and beneficial for everyone.
Hope this helps clarify things and let me know what you find after giving these steps a try happy to help you investigate this further.
Thank you for using the Microsoft Community Forum.Hi thiru2010,
Just checking in to see if the issue has been resolved on your end. If the earlier suggestions helped, that’s great to hear! And if you’re still facing challenges, feel free to share more details happy to assist further.Thank you.
