This guide will show you how to optimise the back-end code of your Power BI reports to make them run faster. We are recognised for our expertise in implementing business intelligence and analytics solutions as the Microsoft Power BI Partner of the Year for 2021.
Based on our experience, we’ve compiled this list of best practises, which includes:
- Enhancing the DAX Syntax
- DAX Functions Optimization
- Avoiding Common Mistakes
Before you start:
Tip #1: Before optimising DAX, always clear your DAX cache.
Internal VertiPaq queries build up your DAX cache. From within DAX Studio, you can clear your cache. You can effectively measure performance gains by resetting your cache.
Improving Your DAX Syntax
1. Use DAX Formatter to format your code
Code that has been formatted is easier to read and maintain. DAX Formatter is a free programme that converts raw DAX into readable code.
2. Use the DISTINCT() and VALUES() functions consistently
If Power BI detects a referential integrity violation, it fills the column with a blank value. Because it can’t check for violations when running direct queries, Microsoft Power Bi Tool adds a blank value to columns.
DISTINCT() and VALUES() are two different functions:
Due to an integrity violation, DISTINCT() does not return any blanks added. A blank is only included in the DISTINCT() function if it is part of the original data.
VALUES(): Returns both original data blanks and blanks added by Power BI as a result of referential integrity violations.
Throughout the report, make sure to use the DISTINCT() and VALUES() functions consistently. Otherwise, the values for blank columns will be inconsistent.
3. Add column and measure references in your DAX expressions
You must eliminate ambiguity in your DAX to ensure that it can be understood and used by anyone. You ensure that anyone can read your DAX at a glance by including column and measure references. Fully qualified column references should always be used, but fully qualified measure references should never be used. You can quickly distinguish between a column and a measure based on whether or not it’s fully qualified this way.
When a measure home table is changed, adding column and measure references ensures that expressions continue to work.
Profit = Orders[Sales] with col reference – Orders[Price]
Without the use of a col reference: [Sales] – [Cost] = Profit
Microsoft Power BI: Deep dive into DAX evaluation context – BRK3060
Optimizing Your DAX Functions
1. Use ISBLANK() instead of =Blank() check
Instead of using the comparison operator = Blank, use the built-in function ISBLANK() to check for any blank values (). IsBlank only checks for blanks, whereas = Blank() returns ‘True’ for either blank values or empty strings.
2. Use = 0 instead of checking for ISBLANK() || = 0
In Power BI, the BLANK value is associated with the data type’s base value. “0” for integers, “(empty string)” for string columns, and “1–1–1900” for date fields correspond to the BLANK value.
ISBLANK() || = 0 performs two checks: first, it determines whether or not a column is BLANK, and then it looks for zeroes. = 0 performs both checks at the same time, speeding up the calculation process.
Use the IN operator to check solely for zero.
3. Use SELECTEDVALUE() instead of HASONEVALUE()
After applying slicers and filters, it’s common to use HASONEVALUE() to see if there’s only one value in a column. When you do this, you must also use the DAX function VALUES(ColumnName) to retrieve that single value.
Internally, SELECTEDVALUE() performs the steps listed above. If there is only one value, it retrieves it automatically, and if there are multiple values, it returns a blank.
4. Use SELECTEDVALUE() instead of VALUES()
If the VALUES() function encounters multiple values, it returns an error. Error functions are frequently used to address errors, which has a negative impact on performance.
Use SELECTEDVALUE instead of VALUES() (). If it encounters multiple values, the SELECTEDVALUE() function returns a blank (instead of an error).
5. Use variables instead of repeating measures inside the IF branch
Ratio = IF([Total Rows] > 10, SUM(Revenue) /[Total Rows], 0)
Measures are calculated in this case in real time, which means the [Total Rows] expression is calculated twice: once for the condition check and then again for the true condition expression.
Correct DAX: VAR totalRows = [Total Rows]; Ratio = IF(totalRows > 10, SUM(Revenue) / totalRows,0)
You can save the resultant measure value in a variable rather than calculating the same expression multiple times. Wherever a variable reference is needed, you can use it. All instances where you call the same measure follow the same variable process. Variables can help you avoid performing repetitive tasks.
It’s important to remember that variables are actually constants.
6. Use DIVIDE() instead of /
If the denominator is zero, / throws an exception. The DIVIDE() function performs an internal check to see if the denominator is zero. If it is, the value specified in a third (extra) parameter is returned.
When using the “/” operator, you must use the IF condition for “invalid denominator” cases. Internally, the DIVIDE() function performs IF checks.
Note: It is preferable to use the “/” operator without an IF check if you are certain the denominator value is not zero.
7. Use KEEPFILTERS() instead of FILTER(T)
Any existing set of filters on a column applied via slicers are overridden by the FILTER function. The KEEPFILTER function does not override the set of filters that already exist. Instead, it employs the intersection of values found in both, preserving the current situation. When performing calculations, use it to maintain any filters applied by slicers or at the report level.
8. Use FILTER(all(ColumnName)) instead of FILTER(values()) or FILTER(T)
Instead of using Table or VALUE, combine the All(ColumnName) and FILTER functions to calculate measures independent of any filters applied to a column (). Consider the following scenario: CALCULATE([Total Sales], FILTER(ALL(Products[Color]), Color = ‘Red’))
If you don’t need to keep the current context, use ALL with the FILTER function. Using expressions instead of the FILTER function to apply filters has the same effect as using the FILTER function. The ALL function in the filter is used to translate this method internally. Consider the following scenario: CALCULATE([Total Sales], FILTER(ALL(Products[Color]), Color = ‘Red’))
Filters should always be applied to the desired column rather than the entire table, as this allows for easier scaling.
pbidax and sqlbi are two sources of information.
9. Use COUNTROWS instead of COUNT
You can count column values with the COUNT function or table rows with the COUNTROWS function in Power BI. If the counted column does not contain any BLANKs, both functions produce the same result.
For three reasons, COUNTROWS is usually the better option:
- It’s more efficient, and will perform better
- It doesn’t consider BLANKs
For example: Sales Orders = COUNT(Sales [OrderDate]) versus
Sales Orders = COUNTROWS(Sales)3. The formula intention is clearer and self-descriptive
10. Use SEARCH() with the last parameter
The last parameter of the SEARCH() DAX function is the value that the query must return if the search string is not found. Instead of using Error functions in conjunction with SEARCH(), you should always use SEARCH() ().
11. ALL vs. ALLExcept
As long as the “exempted” columns are columns on the pivot, ALLEXCEPT() behaves exactly like ALL(), VALUES(). On columns that aren’t on the pivot, ALLEXCEPT() does not keep the pivot context. When using VALUES, use ALL() instead of ALLEXCEPT() ()
Common Mistakes to Avoid
1. Do not change BLANK values to zeros or other strings
Blanks are frequently replaced with zeros or other strings. Power BI, on the other hand, filters out all rows with blank values. This limits the result set and improves performance when viewing results from tables with large amounts of data.
Power BI does not filter unwanted rows when you replace blanks, which has a negative impact on performance.
2. Use (a-b)/b along with variables instead of a/b — 1 or a/b*100–100
To avoid duplicate measure calculations, it is common to use a/b — 1 to calculate a ratio. However, you can achieve the same results by using variables and calculating the ratio with (a-b)/b.
If both a and b are blank, (a-b)/b returns a blank value, and Power BI filters out the values. Because both a and b are integers, the result of a/b — 1 would be -1.
sqlbi is a source of information.
3. Stop using IFERROR() and ISERROR()
When using the FIND() and SEARCH() functions in Excel, the IFERROR() and ISERROR() functions were frequently used. Because FIND() and SEARCH() returned errors if the query did not return the desired result, they were required. The IFERROR() and ISERROR() functions force the Power BI engine to check each row for errors in a step-by-step manner. There is no direct method to state which row returned the error at the moment.
The DAX functions FIND() and SEARCH() provide an extra parameter that the query can pass. If the search string isn’t found, the parameter is returned. The DAX functions FIND() and SEARCH() check if more than one value is returned. They also make certain that no fractions are divided by zero.
You can use situationally appropriate DAX functions like DIVIDE() and SELECTEDVALUE instead of the FIND() and SEARCH() DAX functions (). Internally, the DIVIDE() and SELECTEDVALUE() functions check for errors and return the expected results.
Always remember that DAX expressions can be written in such a way that they never return an error.
4. Do not use scalar variables in SUMMARIZE()
Traditionally, the SUMMARIZE() function has been used to group columns and return the resulting aggregations. The SUMMARIZECOLUMNS() function, on the other hand, is newer and more optimised. Instead, make use of that.
SUMMARIZE() should only be used for grouped table elements that don’t have any associated measures or aggregations. Consider the following scenario: SUMMARIZE(Table, Column1, Column2)
5. Avoid using the AddColumns() function inside measure expressions
By default, measures are calculated iteratively. When iterative functions like AddColumns() are used in measure definitions, Best Power Bi Reports creates nested iterations, which slow down report performance.
6. Check if you can convert your column to a Boolean column
If a column contains only two distinct values, see if it can be converted to a Boolean data type (true/false). When you have a large number of rows, using Boolean data types speeds up the process.
7. Avoid filtering on string columns
Filtering should instead be done with ID columns. If you need to filter by sales location, for example, give each one a numeric ID. This means that instead of string columns, you filter by integer columns. You can now use the VertiPaq engine, which uses value encoding to reduce a column’s memory footprint.
Note that value encoding is only applicable to integers.
8. Work upstream, if possible
Consider creating calculated columns or flags in the back end if certain calculations require complex DAX formulae or if multiple filters are applied repeatedly in DAX measures and calculations.