Forum Discussion
Direct query - Slow performance with calculation group
Hi team,
I got a problem which need your kind suggestions, thanks in advance.
I have a dashboard, it direct query from a PBI dataset (model size: 46M), this PBI dataset imported data from dataflows.
In this dashboard, I created a matrix visual showing more than 30 measures by calculation group items (QoQ, YoY, Previous Y, Previous Q...etc.), the values of these items also changed by the different selected slicers.
Currently the problem is, the performance of this matrix is very slow, I could get the result after loading some times (as workspace admin), but users (without workspace admin/member/contributor role) get resources exceeded error, can't open this visual successfully.
If I choose few measures in visual, the situation is better, it could load successfully, but it loading more time after adding more measures. Actually our original requirement is to enable personlization function for this dashboard, user could add more measures by themselves, but currently we got problem with showing the basic measures...
Do you have any suggestions how to improve the performance for such case? many thanks!
Hi, Tracy000
Here are some strategies to consider to improve performance:
If possible, avoid using computed columns in DirectQuery mode as they can significantly reduce performance.
If your scenario allows it, consider using a hybrid table to cache some frequently accessed data while keeping other data in DirectQuery mode.
Use scheduled cache flushes to improve performance on frequently accessed data.
Rather than displaying all 30 metrics at once, consider providing a way for users to select only the metrics they want. This can be achieved via a slicer or a separate page.
Make sure the DAX expressions in the calculation group are as simple as possible. Avoid using complex logic that can be pre-computed in the data set rather than in real-time.
Consider using an aggregation table to precompute and store the most common aggregations. This reduces the load on the DirectQuery source.
In addition, you can see the following performance optimization articles online:https://www.sqlbi.com/articles/optimizing-card-visuals-in-slow-power-bi-reports/
https://www.youtube.com/watch?v=ScJVQoOWSqc
https://learn.microsoft.com/en-us/power-bi/guidance/report-performance-troubleshoot
https://learn.microsoft.com/en-us/power-bi/guidance/power-bi-optimization
https://radacad.com/why-my-power-bi-matrix-or-table-visual-is-slow
If I have answered your question, please mark my reply as solution and kudos to this post, thank you!
2 Replies
Hi, Tracy000
Here are some strategies to consider to improve performance:
If possible, avoid using computed columns in DirectQuery mode as they can significantly reduce performance.
If your scenario allows it, consider using a hybrid table to cache some frequently accessed data while keeping other data in DirectQuery mode.
Use scheduled cache flushes to improve performance on frequently accessed data.
Rather than displaying all 30 metrics at once, consider providing a way for users to select only the metrics they want. This can be achieved via a slicer or a separate page.
Make sure the DAX expressions in the calculation group are as simple as possible. Avoid using complex logic that can be pre-computed in the data set rather than in real-time.
Consider using an aggregation table to precompute and store the most common aggregations. This reduces the load on the DirectQuery source.
In addition, you can see the following performance optimization articles online:https://www.sqlbi.com/articles/optimizing-card-visuals-in-slow-power-bi-reports/
https://www.youtube.com/watch?v=ScJVQoOWSqc
https://learn.microsoft.com/en-us/power-bi/guidance/report-performance-troubleshoot
https://learn.microsoft.com/en-us/power-bi/guidance/power-bi-optimization
https://radacad.com/why-my-power-bi-matrix-or-table-visual-is-slow
If I have answered your question, please mark my reply as solution and kudos to this post, thank you!