Say you’ve been handed a Power BI workbook which uses a large dataset and runs rather slowly. If your stakeholders don’t want to lose any functionality with the visualisations, you could look to improve performance by optimising the DAX calculations.
There are a few common things you can try to optimise DAX calculations:
1. Using variables where relevant
2. Referencing columns in functions instead of entire tables, where possible
3. Using alternative functions to achieve the same result, if they are newer and more optimised
4. Generally ensuring DAX calculations look through less data whenever they are run
Below are some examples of putting this into practice.
Using Variables:
Variables are constant stored values. In this example, using them instead of repeating the CALCULATE functions for current year, current year sales, and previous year sales means the formula doesn't have to re-calculate them multiple times.
Before:
After:
Specifying column instead of whole table:
When writing DAX functions, when viable it’s best to specify columns relevant to the calculation, instead of the whole table. For example in this function that filters to a specific customer segment, referencing the segment column directly means the calculation looks through the column to filter, rather than the whole table, so takes less time.
Before:
After:
Using the DIVIDE function instead of the operator:
Not only is the DIVIDE function more optimised, but it has a parameter where you can assign a value for when the denominator is zero. Otherwise, you would have to write out an IF statement to deal with this exception, which takes longer to compute.
Before:
After:
Using COUNTROWS:
Using COUNTROWS instead of COUNT or COUNTAX improves performance because COUNTROWS directly counts the number of rows in a table, while COUNTAX needs to evaluate an expression for each row.
Before:
After:
Using KEEPFILTERS instead of FILTER:
FILTER scans the whole table to evaluate each row against the condition, while KEEPFILTERS adds to the existing filter context, rather than creating a new table.
Before:
After:
Using SUMMARIZECOLUMNS instead of SUMMARIZE:
SUMMARIZECOLUMNS is a newer function and is more optimised that SUMMARIZE.
Before:
After:
As well as improving performance, following these tips is general good practice and can help readability of the DAX.
To check performance and see if your changes have helped, you can use the performance analyzer in Power BI. You can find this in the View tab, it’s not already visible. When you click “Start Recording”, you can interact with a few charts, stop the recording, and then view the figures on how long it takes for each report element to load. You can also specifically look at how long it took for the DAX query to run.
Note that for a useful comparison before and after, it’s best to do the exact same interactions when recording performance for the optimised and non-optimised workbooks, and ensure you don’t have anything else running in the background that could affect performance in that moment.