Best Practices Guide

Improve Power BI Performance by Optimizing DAX

July 17, 2019 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 time, clogs premium capacity, increases wait time, and hinders refreshes and report load time.

We deliver rich insights with DAX in hundreds of enterprise-grade Power BI implementations. The following 15 DAX best practices ensure that our dashboard users get the most out of their data.

  1. Use variables instead of repeating measures inside the IF branch
    • Incorrect DAX:
      Ratio = IF([Total Rows] > 10, SUM(Revenue) /[Total Rows], 0)
    • Correct DAX:
      VAR totalRows = [Total Rows];
      Ratio = IF(totalRows > 10, SUM(Revenue) / totalRows,0)
    • Explanation:
      1. In the first expression, because measures are calculated on the fly, the [Total Rows] expression is calculated twice. [Total Rows] is first calculated for the condition check and then for the true condition expression.
      2. 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.
      3. The same variable process applies to all instances where you call the same measure at multiple locations.
    • Reference: https://www.sqlbi.com/articles/optimizing-if-conditions-using-variables/
  2. Use DIVIDE() instead of /
    • The DIVIDE() function has a third (extra) parameter that is returned if the denominator is zero.
    • The DIVIDE() function internally performs a check to validate whether the denominator is zero.
    • 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.
  3. Do not change BLANK values to zeros or other strings
    • It is a common practice to replace blanks with zeros or other strings.
    • Power BI automatically filters all rows with blank values.
    • When viewing results from tables with large amounts of data, Power BI filters the blank value rows, limiting the result set and preventing poor performance.
    • If the blanks are replaced, Power BI does not filter the unwanted rows, affecting performance negatively.
  4. Use (a-b)/b along with variables instead of a/b — 1 or a/b*100–100
    • Using a/b — 1 is the preferred approach to calculate a ratio and avoid duplicate measure calculations.
    • You can achieve the same performance by using variables and using (a-b)/b to calculate the ratio.
    • You should use (a-b)/b for the following reason:
      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.
  5. Use SELECTEDVALUE() instead of HASONEVALUE()
    • Currently, we use the HASONEVALUE() function to check if there is only one value present for a column after applying slicers and filters. We then use the VALUES(ColumnName) DAX function to retrieve the single value.
    • The SELECTEDVALUE() function performs the above steps internally and retrieves the value if there is only one distinct value present for the column. The SELECTEDVALUE() function returns a blank if there are multiple values available.
  6. Stop using IFERROR() and ISERROR()
    • The IFERROR() and ISERROR() functions were widely used in Excel when applying the FIND() and SEARCH() functions. The IFERROR() and ISERROR() functions 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 are also used to ensure nothing is divided by zero. Additionally, the FIND() and SEARCH() functions check if more than one value is returned. 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 return DAX expressions in such a way that they never return an error.
  7. Do not use scalar variables in SUMMARIZE()
    • The SUMMARIZE() function is traditionally used to group columns and return the resulting aggregations.
    • Instead of using the SUMMARIZE() function in this manner, you should use the SUMMARIZECOLUMNS() DAX function. The SUMMARIZECOLUMNS() function is newer and better optimized.
    • You should only use the SUMMARIZE() function to summarize the grouped elements of a table without any associated measures or aggregations. For example:
      SUMMARIZE(Table, Column1, Column2)
  8. Use = 0 instead of check for ISBLANK() || = 0
    • The BLANK value in Power BI is associated with the base value of the data type of a column.
    • The BLANK value corresponds to zero for integers, “(empty string)” for string columns, and “1–1–1900” for date fields.
    • Instead of having two checks (ISBLANK() and comparing with zero), you can directly use = 0, which internally performs both checks.
    • To perform only the check for zero, you can use the IN operator.
  9. Use FILTER(all(ColumnName)) instead of FILTER(values()) or FILTER(T)
    • To calculate measures ignoring all the filters applied to a column, use the All(ColumnName) function along with the FILTER function instead of Table or VALUE(). For example:
      CALCULATE([Total Sales], FILTER(ALL(Products[Color]), Color = ‘Red’))
    • Directly applying filters using expressions and not using 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], Products[Color]= ‘Red’)) -> CALCULATE([Total Sales], FILTER(ALL(Products[Color]), Products[Color]= ‘Red’))
    • It is always better to apply filters to the desired column than to the whole table.
    • Always use ALL along with the FILTER function if there is no need to keep the current context.
    • References:
      1. https://pbidax.wordpress.com/2016/05/22/simple-filter-in-dax-measures/
      2. https://www.sqlbi.com/articles/filter-arguments-in-calculate/
  10. Use KEEPFILTERS() instead of FILTER(T)
    • The normal FILTER function overrides any existing set of filters present for a column applied via slicers with those mentioned in the FILTER parameter.
    • The KEEPFILTER function does not override the current set of filters. Instead, it uses the intersection of the values present in both, thus maintaining the current context.
    • Whenever you need to maintain the set of filters applied using slicers or report-level filters while performing any calculations, we recommend you use the KEEPFILTERS() function.
  11. 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().
  12. 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().
  13. 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 affect performance negatively.
    • Instead of using the VALUES() function, use the SELECTEDVALUE() function. The SELECTEDVALUE() function is safer and returns a blank if it encounters multiple values.
  14. 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 as follows:
      1. DISTINCT(): Does not return a blank that is added due to an integrity violation. The DISTINCT() function includes the blank only if it is part of the original data.
      2. VALUES(): Includes any blank which 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 throughout the report if blank values are not an issue.
  15. 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 affect report performance negatively.

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: