DAX Best Practices Guide

Improve Power BI Performance by Optimizing DAX

July 10, 2020 6 Minutes to Read

Power BI performance issues are often a result of sub-optimal Data Analysis Expressions (DAX) language. DAX is a collection of functions, operators, and constants that you can use in a formula, or expression, to calculate and return one or more values. Stated more simply, DAX helps you create new information from data already in your model.

Optimized DAX provides deep insights, shedding new light on complex data. Inefficient DAX slows processing, clogs premium capacity, increases wait time, and hinders refreshes and report load time.

We have experience delivering rich insights with DAX in hundreds of enterprise-grade Power BI implementations. Use these 20 DAX best practices to ensure your dashboard users get the most out of their data.

  1. Clear the DAX cache before optimizing DAX
    • Caches result from internal VertiPaq queries.
    • Clear your cache from within DAX Studio.
    • Resetting the cache lets you measure effective performance gain.
  2. Format your code
    • Use the DAX Formatter.
    • Formatted code is easier to read and maintain.
  3. Do not change BLANK values to zeros or other strings
    • It is common practice to replace blanks with zeros or other strings.
    • However, Power BI automatically filters all rows with blank values. When viewing results from tables with large amounts of data, this limits the result set and prevents poor performance.
    • If the blanks are replaced, Power BI does not filter the unwanted rows, negatively affecting performance.
  4. Use the DISTINCT() and VALUES() functions consistently
    • Power BI adds a blank value to a column if it finds a referential integrity violation.
    • For a direct query, Power BI adds a blank value to the columns because it cannot check for violations.
    • The DISTINCT() and VALUES() functions differ:
      • DISTINCT(): Does not return a blank that is added due to an integrity violation. The DISTINCT() function includes a blank only if it is part of the original data.
      • VALUES(): Includes any blank that Power BI adds due to referential integrity violations.
    • Maintain consistent usage of the DISTINCT() and VALUES() functions throughout the entire report.
    • Power BI recommends using the VALUES() function if blank values are not an issue.
  5. Use ISBLANK() instead of =Blank() check
    • Use the built-in function ISBLANK() to check for any blank values instead of using the comparison operator = Blank().
  6. Use = 0 instead of checking for ISBLANK() || = 0
    • The BLANK value in Power BI is associated with the base value of a column’s data type
    • The BLANK value corresponds to zero for integers, “(empty string)” for string columns, and “1–1–1900” for date fields.
    • ISBLANK() || = 0 enacts two checks: ISBLANK() and comparing with zero.
    • Use = 0, which internally performs both checks.
    • To perform only the check for zero, use the IN operator.
  7. Use SELECTEDVALUE() instead of HASONEVALUE()
    • It is common practice to use HASONEVALUE() to check if there is only one value present in a column after applying slicers and filters. You also have to use the VALUES(ColumnName) DAX function to retrieve that single value.
    • SELECTEDVALUE() performs the above steps internally. It automatically retrieves the single value if there is one and returns a blank if there are multiple values available.
  8. Use SELECTEDVALUE() instead of VALUES()
    • The VALUES() function returns an error if it encounters multiple values. Normally, users address the error using Error functions, which negatively affects performance.
    • Instead of using VALUES(), use SELECTEDVALUE(). The SELECTEDVALUE() function is safer and returns a blank if it encounters multiple values.
  9. Use variables instead of repeating measures inside the IF branch
    • Incorrect DAX:
      Ratio = IF([Total Rows] > 10, SUM(Revenue) /[Total Rows], 0)
      • Because measures are calculated continuously, the [Total Rows] expression is calculated twice: first for the condition check, then for the true condition expression.
    • Correct DAX:
      VAR totalRows = [Total Rows]; Ratio = IF(totalRows > 10, SUM(Revenue) / totalRows,0)
    • Instead of calculating the same expression multiple times, you can store the resulting measure value in a variable.
    • You can use a variable reference wherever required. The same variable process applies to all instances where you call the same measure.
    • Variables can help you avoid repetitive functions.
    • Note: Be aware that variables are actually constants.
  10. Use (a-b)/b along with variables instead of a/b — 1 or a/b*100–100
    • It is common practice to use a/b — 1 to calculate a ratio and avoid duplicate measure calculations.
    • However, you can achieve the same performance by using variables and using (a-b)/b to calculate the ratio.
    • Here’s why you should use (a-b)/b: If both a and b are blank values, then (a-b)/b returns a blank value and Power BI will filter the values out. a/b — 1 would return -1 as the result because both a and b are integers.
    • Reference: sqlbi
  11. Stop using IFERROR() and ISERROR()
    • The IFERROR() and ISERROR() functions were widely used in Excel when applying the FIND() and SEARCH() functions. They were necessary because FIND() and SEARCH() returned errors if the query did not obtain the required result.
    • The IFERROR() and ISERROR() functions force the Power BI engine to perform a step-by-step execution of each row to check for errors. There is currently no method to directly state which row returned the error.
    • The FIND() and SEARCH() DAX functions provide an extra parameter that the query can pass. The parameter is returned if the search string is not present.
    • The FIND() and SEARCH() DAX functions check if more than one value is returned. They also ensure nothing is divided by zero. You can avoid using the FIND() and SEARCH() DAX functions by using the correct DAX functions such as DIVIDE() and SELECTEDVALUE(). The DIVIDE() and SELECTEDVALUE() functions perform the error check internally and return the expected results.
    • You can always use DAX expressions in such a way that they never return an error.
  12. Use DIVIDE() instead of /
    • / raises an exception if the denominator is zero.
    • The DIVIDE() function internally performs a check to validate whether the denominator is zero. If it is, it returns the value specified in a third (extra) parameter.
    • For “invalid denominator” cases, use the IF condition while using the “/” operator.
    • Note: If you are certain the denominator value is not zero, it is better to use the “/” operator without an IF check. The DIVIDE() function will always perform an IF check internally.
  13. Do not use scalar variables in SUMMARIZE()
    • The SUMMARIZE() function is traditionally used to group columns and return resulting aggregations.
    • However, the SUMMARIZECOLUMNS() function is newer and more optimized. Use it instead.
    • Only use SUMMARIZE() for grouped elements of a table without any associated measures or aggregations. For example: SUMMARIZE(Table, Column1, Column2)
  14. Use KEEPFILTERS() instead of FILTER(T)
    • The FILTER function overrides any existing set of filters on a column applied via slicers.
    • The KEEPFILTER function does not override the existing set of filters. Instead, it uses the intersection of values present in both, thus maintaining the current context. Use it when you want to maintain any filters applied by slicers or at a report level while performing calculations.
  15. Use FILTER(all(ColumnName)) instead of FILTER(values()) or FILTER(T)
    • To calculate measures independent of any filters applied to a column, combine the All(ColumnName) function with the FILTER function instead of using Table or VALUE(). For example: CALCULATE([Total Sales], FILTER(ALL(Products[Color]), Color = ‘Red’))
    • Use ALL along with the FILTER function if there is no need to keep the current context.
    • Directly applying filters using expressions instead of the FILTER function behaves in the same way as mentioned above. This method internally translates using the ALL function in the filter. For example: CALCULATE([Total Sales], FILTER(ALL(Products[Color]), Color = ‘Red’))
    • It is always better to apply filters to the desired column than to the whole table for the sake of scalability.
    • References:
  16. Avoid using the AddColumns() function inside measure expressions
    • Measures are calculated iteratively by default.
    • If measure definitions use iterative functions such as AddColumns(), Power BI creates nested iterations, which negatively affects report performance.
  17. Use the correct data types based on column values
    • If there are only two distinct values in a column, check if it can be converted to a Boolean data type (true/false).
    • This speeds up processing when you have large number of rows.
  18. Use COUNTROWS instead of COUNT:
    • Use the COUNT function to count column values, or we can use the COUNTROWS function to count table rows. Both functions will achieve the same result, providing that the counted column contains no BLANKs. Sales Orders = COUNT(Sales [OrderDate]) Sales Orders = COUNTROWS(Sales)
    • There are three reasons why the second measure definition is better:
      1. It's more efficient, and will perform better.
      2. It doesn't consider BLANKs contained in any column of the table.
      3. The formula intention is clearer and self-describing.
    • Reference: DAX-CountRows
  19. Use SEARCH() with the last parameter
    • The SEARCH() DAX function accepts the last parameter as the value that the query must return if the search string is not found.
    • You should always use the SEARCH() function instead of using Error functions along with SEARCH().
  20. ALL vs. ALLExcept
    • ALLEXCEPT() behaves exactly like ALL(), VALUES() as long as the “exempted” columns are columns on the pivot.
    • ALLEXCEPT() does NOT preserve pivot context on columns that are not on the pivot.
    • Use ALL() instead of ALLEXCEPT() when using VALUES()

Power BI Performance Guide

To further improve Power BI performance, check out our Power BI best practice guide.

References

Microsoft offers additional documents that provide a high-level framework for best practices. We strongly encourage you to review the following documents: