DAX is all about the calculation part of the Power BI tool where we are writing DAX queries to get the output from the data in the data model. A formula in DAX is called a measure. The more measures we write the heavier the report will be. But this is not the only reason for a slow report. Without measures also, a report can load slowly.
If you create a report & that is loading extremely slowly for your client, then that is very irritating. The performance of your report plays a very important role along with a good analysis.
In this blog, we will discuss the ways that will help you to optimize your DAX queries & ultimately your whole Power BI report. The first step would be to understand & identify the bottlenecks like where we are falling short with respect to performance. And then the solution can be implemented. Here in this blog, we will talk about both simultaneously point by point.
21 Ways to Optimize DAX Queries & Power BI Report
1. Hiding unused columns
Your DAX query may consist of many unused columns. Prefer hiding those unused columns to optimize your DAX query.
In the report view, right-click on the unused columns and hide them. The columns will only disappear from the report view and are available in the rest of the views like data & model views.
2. Appropriately use the error functions
It is very important to use the error functions appropriately. For example, avoid using the ISERROR and IFERROR functions. Although these functions are very helpful & can contribute to writing easy-to-understand output they degrade the performance of the query.
Let’s understand the major reason for the low performance of any query first. Most of the issues happen due to unexpected blank values, zero values & unexpected data conversion. So as a suggestion, prefer writing expressions and developing models using defensive strategies. Strategies like, using a power query editor for the transformation of data so that we are taking care of blank & zero values in advance. Also, make sure the correct data types are selected during data transformations.
3. Clear the cache first
Always prefer clearing the cache first before optimizing your DAX query. Other users may possibly not use the cache present in the calculation. As a result, you will receive faster results.
Following is the path you need to follow to do the same.
File -> Options and settings -> Options -> Data Load -> Data Cache Management Options -> Clear Cache
4. Visualization Load Time
Using a performance analyser pane, check the load time of each & every visual to identify the culprit visual. Performance analyser pane is a very powerful in-built feature that is telling you what is going on behind the curtains. Whichever visual is loading late, can be replaced.
5. Data Model (Star Schema)
If the data model is complex, then the control movement across tables will take a lot of time & query will take extra time to process the output. Reduce the complexity of the data model. This is the reason, a star schema is preferred over a snowflake schema.
6. Number of Visuals
For every visual, a new query is generated so having a lot of visuals is also not a very good idea. If 5 visuals of your report can be replaced by a single DAX Query, then go for it
7. Appropriately use the DIVIDE FUNCTION over DIVIDE OPERATOR
The main purpose of designing the DIVIDE FUNCTION was to handle division by zero because it has the third parameter to mention the alternate result. There may be situations where the denominator is zero or blank. In situations like these, we recommend that you use the DIVIDE FUNCTION instead of the divide operator.
Use of DIVIDE OPERATOR: If the denominator is Zero or Blank then there is no option to mention the alternate result.
Profit Margin = Sum(Orders[Profit])/ Sum(Orders[Sales])
Use of DIVIDE FUNCTION: If the denominator is Zero or Blank then there is an option available to mention the alternate result.
Profit Margin = DIVIDE(Sum(Orders[Profit]), Sum(Orders[Sales]), 0)
8. Avoid converting BLANKs to values
In the DIVIDE FUNCTION above, we have the blanks into values (0). This is called a poorly designed model because if we create a matrix with the Customer ID and Profit Margin then it will display 0 for the customers who have no sales or 0 sales. We need to avoid converting BLANKs to values for an optimized model.
Profit Margin = DIVIDE(Sum(Orders[Profit]), Sum(Orders[Sales]))
If we use the above formula, then the matrix will only display the Profit Margin for the customers who have made sales.
9. Avoid using the FILTER function as a filter argument & Prefer using KEEPFILTER() function
While using the filter function, it is suggested to pass the filter arguments as Boolean expressions because the tables in the data model are explicitly optimized to filter columns in this way. Normally, to pass the filter arguments, we use table expressions but that is not the optimized way. So, try to understand where all we need to use the table expressions.
- Boolean expressions cannot refer to the columns from multiple tables.
- They cannot even refer to a measure.
- Boolean expressions cannot use nested CALCULATE functions.
- And cannot use functions that scan or return a table.
So, for the complex filter arguments, we must go ahead with table expressions only. And wherever we can manage with Boolean expressions (KEEPFILTERS Function), it is recommended to prefer that.
10. Fully qualify your column references
A fully qualified column reference means that the table name precedes the column name. This practice helps in optimization because there won’t be any ambiguous references to be checked & it provides better formula readability.
For example: Create a calculated column with the name Cost.
Only with column names
Cost = [Sales] – [Profit]
With fully qualify column reference
Cost = Orders[Sales] – Orders[Profit]
11. Never fully qualify your measure references
In reverse to the column, it is recommended to never fully qualify your major references. This point is somehow related to fully qualifying your column references. Imagine a situation in which you did not qualify your measure reference but fully qualified your column reference. In this case, you will quickly determine whether it is a measure or a column based on whether it is qualified or not.
Coming back to the point. Every measure has a home table so if you fully qualify your measure reference and we change the home table then all the measures or visuals where we have used that measure will break.
12. Use variables to improve your DAX formula
The concept of using variables in the DAX Formula is highly recommended.
For example:
Test =
VAR up=unichar(9995)
VAR down=unichar(10160)
Return if([Growth]>0, up, down)
It will help you write more complex and efficient calculations easily. In addition, formula readability, performance, and reliability will be improved. Plus, the same variable can be used multiple times in the same formula.
13. Use the COUNTROWS function instead of the COUNT function
The COUNT function is basically used to count column values. On the other hand, the COUNTROWS function is basically used to count table rows. If there are no blanks in the column, both functions provide similar results. We would still recommend that you use COUNTROWS instead of COUNT. There are three reasons for our recommendation.
1) The COUNTROWS function will perform better as it is more efficient.
2) The COUNTROWS function does not consider blanks.
3) The formula intention of the COUNTROWS is self-descriptive and clear.
14. Prefer using the DAX Formatter to format your code
Formatted codes are easy to read and maintain. This is where the DAX Formatter comes into action. It allows users to convert raw DAX codes into readable codes. And, surprisingly, DAX Formatter is a free tool. Hence, we recommend that you use the DAX Formatter to format your code. This will end up optimizing your DAX query. This is an online tool (no exe file is required to be downloaded). You can use it from here.
15. Use SEARCH() with the last parameter
The SEARCH function in DAX is not as same as in Excel. In case the search string is not found, the SEARCH() DAX function accepts the last parameter as the outcome of the query. Hence, we suggest that instead of using the IFERROR function along with the SEARCH(), only use the SEARCH() function.
The syntax is as follows:
SEARCH(<find_text>, <within_text>[, [<start_num>][, <NotFoundValue>]])
16. Avoid filtering on string columns
To optimize your DAX query, we would suggest that you avoid filtering on string columns. Prefer ID columns for filtering instead.
17. Reuse Measures
It is recommended to reuse the measures instead of writing the same code again. It helps to optimize the whole report.
Example:
Total Sales = Sum(Orders[Sales])
All Sales = Calculate([Total Sales], All(Orders))
Sales% = Divide([Total Sales], [All Sales])
18. Avoid using the entire table with ALL Function
All() function can be used for some specific columns also and for the entire table also. It is recommended to use the same for specific columns wherever possible to optimize the DAX query.
When used on the entire table
All Sales = Calculate(Sum(Orders[Sales]), All(Calendar))
When used on specific columns
All Sales = Calculate(Sum(Orders[Sales]), All(Calendar[Fiscal Year], Calendar[Fiscal Month]))
19. Select the right table to create the measures
Prefer to create the measures in the table where the columns are present. This way, Vertipaq Engine doesn’t need to iterate in other tables for the related values.
20. Calculated Column Vs Column in Table
Try to avoid calculated columns because they are evaluated every time the model is updated. Better to create a column in the table itself using Power Query Editor.
21. DAX Studio
The last tip is to learn DAX Studio. DAX Studio is an external tool that helps to optimize the DAX Queries. It captures a lot of detailed information about the measures so it can help you to troubleshoot why your DAX code is running slow.
Conclusion
This was all about the 21 ways in which you can optimize your DAX queries. Apart from these ways, it is recommended to understand the two engines on which the whole DAX is based upon. That is going to help you to write better DAX formulas.
Happy Learning!
