This article helps you to accelerate your Power BI reports by improving their back-end code.

Effective use of DAX functions:

Adding DISTINCT () and VALUES () functions persistently:

In DAX, the blank value necessitates special consideration. Power BI assigns a blank value to the column when connecting directly to a query since it is unable to check for referential integrity breaches. (Assume Referential Integrity will be activated when you connect data sources to Direct Connectivity Mode, adding blank values due to the Left Outer Join between the query that is enabled by default.)
Using DISTINCT () can only add blank values present in the original data.
Using VALUES (), along with original data blank values Power BI adds blank values.
For example: There are two tables named as Fact and Dim tables as shown below. Count Distinct and Count Values are calculated. Now we can see the difference of using DISTINCT () and VALUES () function as explained above.

         

 

Blanks in DAX:

To check for any blank values in your data always use the built-in function ISBLANK () rather than using the comparison operator = Blank (). Since =Blank () checks for either blank values or empty strings whereas ISBLANK () checks for core blank values.
Sometimes the blank values in Power BI depends on the column’s data type. For example, “0” for integers, “empty strings” for text columns, “1-1-1900″ for date columns. Therefore, to check for blank values use ISBLANK () and to check for zeroes use IN operator.

Importance of SELECTEDVALUE (): 

SELECTEDVALUE () function skips most of the steps to obtain the single value. If there are multiple values, it returns blank. Some users prefer to use function HASONEVALUE () followed with VALUES () function after applying slicers and filters to retrieve a single value in a column. But sometimes VALUES () function fails and gives an error if it finds multiple values which thereby affects performance.
Hence, use SELECTEDVALUE () instead of VALUES () to return only one value in a column. If there are multiple values, SELECTEDVALUE () returns blank rather than giving an error.

DIVIDE () vs “/ “:

DIVIDE () function evaluates to verify whether the denominator is zero.

DAX Syntax – DIVIDE(<numerator>, <denominator> [, <alternate result>]).

The third parameter lets us to specify the alternate result if the result returns other than 0. If you are beyond doubt that result cannot be zero in any case, then “/” divide operator can be used.

KEEPFILTERS () instead of FILTER(T):

The FILTER(T) function disregards the other slicers present in report. To allow the slicers work along with your calculation use KEEPFILTERS () function which does not override with your existing filters.

COUNTROWS vs COUNT:

If there are no blank values in the counted column COUNTROWS () and COUNT () function gives the same value.

For example:  Count Orders = COUNT (Orders [Order Date])

Total Orders = COUNTROWS(Orders)

COUNTROWS is specifically better option for three main reasons:

It does not include blanks.
The formula description is clear and self-explanatory.
It performs better and more efficiently.

Use FILTER () instead of ALL () in calculated columns:

In Calculated columns, if you use ALL () function, DAX will keep all the values in that column even though there are filters applied on other columns. The easiest way to attain the same results is use FILTER () function, it will adjust your used column with the other columns those are being filtered out.

Some ground rules to follow:

Always reuse your DAX measures if there have repeated calculations in another DAX expressions.
Use proper names of measures and calculated columns which are self-explanatory about the calculation.
Add a description to provide additional information about the measure.
Always use code formatter for ease of readability and clarity.
DAX formatter is a free tool which transforms your raw DAX code into readable code.
Bypass complex calculations that are not needed for your report into simple terms.

Happy Reading!!