DAX query optimisation

Anonymous

Not applicable

2 years ago

As far as I know, the summarize() function creates a table, and creating a table is an operation that generally consumes a large amount of time, so you might consider optimizing performance by removing extended columns from summarize() and adding them using the addcolumns() function.

Optimize SUMMARIZE with ADDCOLUMNS in Dax #ssas #tabular #dax #powerpivot - SQLBI

Best practices using SUMMARIZE and ADDCOLUMNS - SQLBI

There are also some suggestions for optimizing the model here:

Remove unused tables or columns, where possible.
Avoid distinct counts on fields with high cardinality – that is, millions of distinct values.
Take steps to avoid fields with unnecessary precision and high cardinality. For example, you could split highly unique datetime values into separate columns – for example, month, year, date, and so on. Or, where possible, use rounding on high-precision fields to lower cardinality – (for example, 13.29889 -> 13.3).
When connecting to data sources via Direct Query, consider indexing columns that are commonly filtered or sliced again. Indexing greatly improves report responsiveness.

You can use DAX Studio to optimize the inspection of DAX statements from a performance perspective and use the Performance analyzer to check the performance of each of your report elements when users interact with them, and which aspects of performance consume the most (or least) resources

https://daxstudio.org/

https://docs.microsoft.com/en-us/power-bi/create-reports/desktop-performance-analyzer

This is the related document, you can view this content:

Optimizing DAX expressions involving multiple measures - SQLBI

Improve Power BI Performance by Optimizing your DAX | by MAQ Software | MAQ Software | Medium

All the secrets of SUMMARIZE - SQLBI

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.