Essential checklist to keep your Power BI Solutions optimized. Part III. Optimizing DAX Formulas
This third article will cover how to optimize the DAX Formulas of your data model
– Convert Multiple Measures to Variables: This will minimize the number of measures on your data model and improve their performance.
– Use the DIVIDE function for division instead divide operator (/): This function is optimized to check division by zero which generates a significant performance gain.
– Avoid using the IFERROR function: It’s better to avoid using the ISERROR and IFERROR functions. Instead, apply defensive strategies when developing the model as:
- Apply Power Query transformations to remove or substitute invalid or missing values, and to set correct data types.
- Use the IF function to determine whether an error result would occur or error-tolerant functions.
– Reduce usage of calculated columns that use the RELATED function.
– Use SELECTEDVALUE() instead of HASONEVALUE().
– Use COUNTROWS instead of COUNT because it’s more efficient and performs better.
– Write Explicit measures: You will have more flexibility and will be able to reuse them within other DAX formulas.
Implicit measure: This is when you use a column with numeric values directly in a visualization, you will identify it because each numeric column has the Sigma (or sum) icon next to it.
Explicit measure: These are the ones created using a DAX formula, they show a calculator icon beside them.
I recommend that you use explicit measures even for simple measures such as sum, count, average, etc. and then hide the corresponding column to avoid confusing users by having both the column and the measure in the data model.
– Use DAX Formatter to format your code: The code will be easier to read and maintain.
– Use performance analyzer to identify the DAX formulas with longer rendering time.
– Use DAX Studio to troubleshoot the slowest formulas: This tool will help you to speed up the troubleshoot process as you can modify the DAX code there directly and when running it know in detail the performance impact. I recommend you check out this article that goes deeper in this topic.
Click here to read the last part of this series.
