DAX Performance

[Mcalderon98]

Hey! 

I am curretnly working on a report that use a inforiver matrix which combines calculation groups and measures. For this matrix we use 6 measures and 26 calculation groups.

The model and data is complex so we know it will be slow, but we want to improve as much as we can the performance, meanining that the user just wait less than a min for the matrix to load. 

Is there any practice, tip or something you can recommend to improve the performance? Important to mention - the report has a direct connection to the semantic model with all the measures and logics (source dbricks)

THANK YOU!

[v-sshirivolu]

Hi @Mcalderon98 ,
Since your report is using DirectQuery with Databricks and has 6 measures with 26 calculation groups inside an Inforiver matrix, the main performance problem is happening because there are too many calculation combinations. Each measure gets recalculated for every row, column, total, and each calculation group item, which creates heavy query load and large SQL queries in Databricks. To improve performance, try to reduce dynamic calculations and replace some calculation groups with normal measures wherever possible. Avoid complex DAX patterns like iterators or nested SELECTEDMEASURE() inside calculation items, and do not use high-cardinality fields in the matrix. Reducing the matrix detail level will also reduce the number of combinations. Use Performance Analyzer and DAX Studio to check whether the issue is in DAX or SQL. If SQL is slow, then optimize Databricks with better partitioning, indexing, or pre-aggregated tables. You can also use aggregation tables or hybrid mode, where summary data runs in Import and detailed data remains in DirectQuery. Overall, keeping calculations simple and reducing dynamic logic will improve performance in this scenario.

Refer these Microsoft Docs -
https://learn.microsoft.com/en-us/power-bi/guidance/directquery-model-guidance
https://learn.microsoft.com/en-us/power-bi/guidance/power-bi-optimization
https://learn.microsoft.com/en-us/power-bi/transform-model/aggregations-advanced
https://learn.microsoft.com/en-us/power-bi/create-reports/performance-analyzer

[v-sshirivolu]

Hi @Mcalderon98 ,

I hope the above details help you fix the issue. If you still have any questions or need more help, feel free to reach out. We’re always here to support you

[v-sshirivolu]

Hi @Mcalderon98 ,

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.

[nathancwatkins]

Can't give you specific advice without seeing your code or your data model, but with a matrix in general, make sure that everything is collapsed by default.

--Nate