DAX Best approaches in Power BI

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:

1. 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.)
2. Using DISTINCT () can only add blank values present in the original data.
3. 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:

1. To 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.
2. 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 (): 

1. 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.
2. 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 /:

Revolutionize Your Business With Generative AI

From product design and software development to virtual agents, content creation, and reporting, GenAI is transforming business. Our AI experts help you unlock GenAI’s full potential and drive growth.

Let’s Get Started

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. Countrows directly works with rows in a table whereas Count has dependency on column.

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

Total Orders = COUNTROWS(Orders)

COUNTROWS is specifically better option for three main reasons:

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

Some ground rules to follow:

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

Happy Reading!!