Direct query - Slow performance with calculation group

[Tracy000]

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!

[hackcrr · Accepted solution]

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!

[hackcrr · Accepted solution]

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!

[Tracy000 · Accepted solution]

@hackcrr 
Many thanks for your reply and suggestions. 
We found the big impact for our dashboard, it was caused by the complex dynamic RLS we set up.

After we replaced the dynamic RLS with normal RLS, the performance is good now.

Thank you!