10 DAX Best Practices

DAX is a powerful language for data modeling and analysis, and it has a number of best practices that can help you get the most out of it. In this article, we’ll discuss 10 of those best practices, including how to use DAX to simplify complex calculations, how to optimize DAX code for performance, and how to make DAX code more readable.

1. Use native DAX functions instead of Excel formulas

DAX is a formula language, and as such, it is designed to work with data in a tabular format. When you use an Excel function inside a DAX formula, DAX first has to convert the data into an Excel range, which can be time-consuming and can lead to errors.

Furthermore, many Excel functions don’t work the same way in DAX. For example, the VLOOKUP function in Excel only looks up values from the leftmost column of a table, whereas the DAX LOOKUPVALUE function can look up values from any column.

Finally, using native DAX functions will make your formulas more readable and easier to maintain.

2. Avoid using the ALL() function in calculated columns

When you use the ALL() function in a calculated column, DAX will keep the values of that column even when you apply filters to other columns in the table. This can lead to incorrect results, especially if you’re not aware that the ALL() function is being used in your calculations.

A better way to achieve the same result is to use the FILTER() function instead of ALL(). The FILTER() function will only return the values that are relevant for the current filter context, so it will automatically adjust itself when filters are applied to other columns in the table.

Using the FILTER() function instead of ALL() is one of the most important DAX best practices because it can help avoid many potential errors in your calculations.

3. Avoid using row context inside a table scan

When DAX calculates a formula that uses a row context, it first creates a virtual table with all the columns in the current context. For example, if you have a filter on the ‘Country’ column, DAX will create a virtual table that contains all the rows of the ‘Country’ column.

Then, for each row in the virtual table, DAX will evaluate the formula. This process is called ‘row by row evaluation’.

However, when DAX scans a table, it doesn’t create a virtual table. Instead, it reads all the values in the table and stores them in memory. Then, for each row in the table, DAX will evaluate the formula.

This process is called ‘table scan’.

Table scans are much faster than row by row evaluations because they don’t require DAX to create a virtual table. However, if you use a row context inside a table scan, DAX will have to create a virtual table for each row in the table, which will slow down the calculation.

Therefore, it’s important to avoid using row context inside a table scan. You can do this by using the SUMMARIZE function to create a summary table that doesn’t contain any row context.

4. Avoid nested IFs and SWITCH expressions

Nested IFs and SWITCH expressions can be difficult to read and understand, making it hard for other people to work with your DAX code. They can also be slow to execute, as each level of nesting needs to be evaluated before the final result can be returned.

Instead of using nested IFs or SWITCH expressions, try to use the SUMMARIZE function to break down your data into smaller groups, which can then be evaluated separately. This will make your DAX code easier to read and understand, and can improve performance too.

5. Avoid complex calculations that are not needed for your report

DAX is a powerful language that can be used to create complex calculations. However, these complex calculations come at a cost. The more complex the calculation, the longer it will take to execute. Therefore, if you don’t need a complex calculation for your report, avoid creating it.

It’s also important to note that complex calculations can make your reports harder to understand. If you find yourself creating a complex calculation that you can’t explain in simple terms, it’s probably best to avoid using it.

6. Avoid unnecessary CALCULATE calls

CALCULATE is the most expensive function in DAX. It’s so expensive that it can often be the difference between a query taking seconds or minutes to run. So, if you can find another way to get the same result without using CALCULATE, you should do it.

There are often multiple ways to get the same result in DAX. For example, you might be able to use a filter instead of CALCULATE, or you might be able to use a simpler expression.

It’s not always easy to know whether you can avoid using CALCULATE, but there are some general rules of thumb that can help. For example, if you’re only using CALCULATE to filter a single column, you might be able to use a simpler expression. Or, if you’re using CALCULATE to filter a table, you might be able to use a relationship instead.

Of course, there are exceptions to every rule, so you’ll need to use your own judgement to decide whether CALCULATE is really necessary. But, in general, avoiding CALCULATE will make your DAX code more efficient and easier to maintain.

7. Avoid unnecessary use of EARLIER

DAX provides the EARLIER function to enable you to access values from a previous row in the same column. However, this function can be computationally expensive, so it’s important to use it sparingly and only when absolutely necessary.

One way to avoid unnecessary use of EARLIER is to restructure your DAX measures to use iterators instead. Iterators are functions that repeat a process for each row or cell in a column or table. By using an iterator, you can often rewrite a measure without needing to use EARLIER at all.

For example, suppose you have a DAX measure that uses the SUMX function to sum a column of values. The SUMX function internally uses the EARLIER function, so if you can rewrite your measure to use an iterator like SUM instead, you’ll likely see a performance improvement.

It’s also important to note that there are some DAX functions that don’t work well with EARLIER. In particular, the FILTER and ALL functions can give incorrect results when used together with EARLIER. So, if you’re using these functions in your DAX measures, be sure to test them carefully to ensure they’re giving the correct results.

8. Avoid unnecessary use of SUMMARIZE

SUMMARIZE is an expensive operation, and it can often be avoided by using other DAX functions such as FILTER, CALCULATE, or even the simpler SUM and COUNT.

For example, let’s say you want to count the number of rows in a table. You could use the following DAX:

COUNTROWS(MyTable)

Or you could use SUMMARIZE like this:

SUMMARIZE(MyTable, MyTable[Column1], “Count”, COUNTA(MyTable[Column1]))

The first version is much simpler and more efficient. So whenever possible, avoid using SUMMARIZE when another function will do.

9. Avoid unnecessary use of DISTINCT

When you use the DISTINCT function in DAX, it will scan all of the values in the column you’re using it on. This can be a very time-consuming operation, especially if the column is large or contains many duplicates.

It’s important to only use DISTINCT when absolutely necessary. If you can get the same results without using it, then don’t use it. For example, if you’re trying to count the number of unique values in a column, you could use the COUNTAX function instead of DISTINCT.

COUNTAX will only count the values that are not blank, so it will automatically remove any duplicates. This makes it much faster than DISTINCT, and it’s just as accurate in most cases.

10. Always use USERELATIONSHIP when possible

When you use USERELATIONSHIP, DAX will automatically apply any filters that exist on the active relationship. This means that if you have a filter on a date column in one of your tables, DAX will automatically apply that filter to any columns from related tables that you include in your measures.

This is extremely important because it ensures that your measures are always accurate, and it also makes it much easier to write complex measures. If you were to try to write a measure without using USERELATIONSHIP, you would need to manually apply all of the filters from the active relationships, which would be very tedious and error-prone.