Forum Discussion
Advocate IPower BI Performance Issue
I am facing Power BI Performance Issue, My dashboard has calculation groups and has multiple caclulations that are interrelated.
The caculation part cannot be altered since those caculations are mandatory.
Also the matrix/table visual does not load data on the Desktop only shows data on Server, every filter interaction is very slow.
Can anyone suggest how to improve the performance?
If the visual shows “Query has exceeded the available resources” and every interaction is slow in Desktop, this is usually one of these:
-
the DAX query generated by the matrix is too expensive (high cardinality + lots of measures + calc groups),
-
the Desktop machine is hitting memory/CPU limits sooner than the Service capacity,
-
or the model is forcing the engine into row-by-row / non-folding patterns.
1) Model-level improvements
These usually help a lot:
-
Reduce cardinality:
-
don’t use long text keys in relationships (use integer surrogate keys)
-
split datetime into date (and maybe hour) if you don’t need full timestamp
-
-
Ensure a proper star schema:
-
dimensions filter facts (single-direction)
-
avoid bi-directional unless absolutely needed
-
-
Remove unused columns / reduce string columns in facts
2) Identify what’s slow
Open DAX Studio → connect to the Desktop model → run:
-
Server Timings
-
Query Plan
Then refresh the slow visual in Power BI Desktop to capture the query.
What to look for:
-
SE (Storage Engine) time high → model / relationships / cardinality / storage mode issue
-
FE (Formula Engine) time high → DAX patterns, calc groups, iterators, context transitions
-
# of Storage Engine queries very high → calc group causing measure re-evaluation many times
3) Create pre-aggregations at the source
If you can’t change the calculation logic, one of the most effective performance options is to reduce the amount of data the engine must scan by introducing pre-aggregated fact tables in the source (or Lakehouse/Warehouse) and using them in the model.
4) Avoid iterator functions
As a general rule, try to avoid iterator functions such as SUMX, FILTER, ADDCOLUMNS, RANKX on large tables whenever possible. These functions execute row by row in the Formula Engine, which is significantly slower than simple aggregations pushed to the Storage Engine. In models with calculation groups and matrix visuals, iterators can multiply the amount of work per cell and quickly lead to slow interactions or “Query has exceeded the available resources” errors.
-
7 Replies
- cengizhanarslan
Super User
If the visual shows “Query has exceeded the available resources” and every interaction is slow in Desktop, this is usually one of these:
-
the DAX query generated by the matrix is too expensive (high cardinality + lots of measures + calc groups),
-
the Desktop machine is hitting memory/CPU limits sooner than the Service capacity,
-
or the model is forcing the engine into row-by-row / non-folding patterns.
1) Model-level improvements
These usually help a lot:
-
Reduce cardinality:
-
don’t use long text keys in relationships (use integer surrogate keys)
-
split datetime into date (and maybe hour) if you don’t need full timestamp
-
-
Ensure a proper star schema:
-
dimensions filter facts (single-direction)
-
avoid bi-directional unless absolutely needed
-
-
Remove unused columns / reduce string columns in facts
2) Identify what’s slow
Open DAX Studio → connect to the Desktop model → run:
-
Server Timings
-
Query Plan
Then refresh the slow visual in Power BI Desktop to capture the query.
What to look for:
-
SE (Storage Engine) time high → model / relationships / cardinality / storage mode issue
-
FE (Formula Engine) time high → DAX patterns, calc groups, iterators, context transitions
-
# of Storage Engine queries very high → calc group causing measure re-evaluation many times
3) Create pre-aggregations at the source
If you can’t change the calculation logic, one of the most effective performance options is to reduce the amount of data the engine must scan by introducing pre-aggregated fact tables in the source (or Lakehouse/Warehouse) and using them in the model.
4) Avoid iterator functions
As a general rule, try to avoid iterator functions such as SUMX, FILTER, ADDCOLUMNS, RANKX on large tables whenever possible. These functions execute row by row in the Formula Engine, which is significantly slower than simple aggregations pushed to the Storage Engine. In models with calculation groups and matrix visuals, iterators can multiply the amount of work per cell and quickly lead to slow interactions or “Query has exceeded the available resources” errors.
Revati25I also have many to many relationship, to break this to many to one I will have to create Bridge Tables for every single filter I have to use on the dashbord and I have multiple tables.
Due to the many to many relationship I was facing some filtering issues from caculation pov, so i had to use TREATAS Function.
Which is also inturn making the dashboard loading slow.
Are there any alternate functions that I can use that will replicate the functionality of TREATAS but is also not a costly function?
- cengizhanarslan
Instead that try adding surrogate keys to build a proper model if possible. That would be the best option to avoid many-to-many relations.
-
Hi Revati25 ,
The OP is seeking guidance on enhancing Power BI report performance while being restricted by mandatory calculation groups and interdependent DAX logic that cannot be altered. They are experiencing slow interactions, matrix or table visuals failing to load in Power BI Desktop, and resource limit errors in the Service. The goal is to identify solutions at the model, engine, or data level to improve usability and responsiveness, particularly during filtering and interactions in Desktop, without modifying or removing existing calculations.
The super user response effectively addresses the OP’s requirements by respecting these constraints. It recognizes that calculation logic is fixed and emphasizes key performance factors such as model structure, cardinality, differences between Desktop and Service resource limits, Storage Engine versus Formula Engine considerations, and utilizing tools like DAX Studio to diagnose bottlenecks. The answer also suggests practical alternatives, including model optimization and pre-aggregation, which are well aligned with the OP’s needs in this context.
Thank you.Hi Revati25 ,
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.
Hi Revati25 ,
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.
