Mastering DAX: Essential Best Approaches for Power BI Users

shape
shape
shape
shape
shape
shape
shape
shape
Mastering DAX: Essential Best Approaches for Power BI Users

Data Analysis Expressions (DAX) is a powerful formula language used in Power BI to analyze data and create custom calculations. There are various ways to write DAX formulas, and it can be overwhelming for beginners. In this article, we will explore some of the best approaches for writing efficient and effective DAX formulas in Power BI.

DAX functions:
Adding DISTINCT () and VALUES () functions:
  1. When working with DAX in Power BI, blank values require special attention. When connecting directly to a query, Power BI assigns a blank value to the column because it cannot verify referential integrity breaches. However, when connecting data sources to Direct Connectivity Mode, Referential Integrity is assumed to be activated, and blank values are added due to the Left Outer Join between the query, which is enabled by default.
  2. The DISTINCT() function in DAX only adds blank values that exist in the original data.
  3. On the other hand, using the VALUES() function along with the original data adds blank values in Power BI.

For instance, if we calculate Count Distinct and Count Values on two tables named Fact and Dim, we can observe the distinction between using DISTINCT() and VALUES() functions, as discussed above.

Count Distinct and Count Values

calculate Count Distinct and Count Values

Use of Blanks in DAX:
  1. To properly handle blank values in Power BI, use the built-in function ISBLANK() instead of the comparison operator =Blank(). The latter checks for both blank values and empty strings, whereas ISBLANK() only checks for actual blank values.
  2. The way Power BI handles blank values depends on the data type of the column. For instance, integers may have “0” as a blank value, text columns may have empty strings, and date columns may have “1-1-1900“. To check for blank values, use the ISBLANK() function, and to check for zeroes, use the IN operator.
Significance of SELECTEDVALUE ():
  1. The SELECTEDVALUE () function simplifies the process of obtaining a single value by skipping several steps. However, it returns a blank value if there are multiple values. While some users prefer to use the HASONEVALUE () function followed by the VALUES () function after applying slicers and filters to obtain a single value in a column, this approach can sometimes lead to unexpected results.
  2. Therefore, it is advisable to opt for SELECTEDVALUE () over VALUES () to retrieve a single value in a column. This function returns blank instead of throwing an error if there are multiple values present.
DIVIDE () vs /:

The DAX DIVIDE() function checks for denominator zero error. It has a syntax of DIVIDE(<numerator>, <denominator> [, <alternate result>]). The third parameter allows for an alternate result when the result is not 0. If there is the certainty that the result won’t be zero, the divide operator “/” can be used.

KEEPFILTERS () instead of FILTER(T):

To prevent the FILTER(T) function from ignoring other slicers in your report, you can use the KEEPFILTERS () function. This function works in tandem with your existing filters and does not override them.

COUNTROWS vs COUNT:

When there are no blank values in the column being counted, both the COUNTROWS() and COUNT() functions return the same value. However, COUNT() function is dependent on the column, while COUNTROWS() works directly with the table rows. For instance, Count Orders = COUNT (Orders [Order Date]) and Total Orders = COUNTROWS(Orders). Overall, COUNTROWS() is preferable for three main reasons-

  1. The COUNTROWS() function is better than COUNT() if you want to exclude blank values.
  2. The description of COUNTROWS() is straightforward and easy to understand.
  3. COUNTROWS() performs faster and more efficiently than COUNT().

Some basic practices to follow:

  1. Reuse DAX measures to avoid repetitive calculations in other expressions.
  2. Give proper names to measures and calculated columns that clearly describe their calculations.
  3. Include a description to provide additional information about the measure.
  4. Use a code formatted to improve the readability and clarity of your DAX code.
  5. DAX formatter is a free tool that can transform raw DAX code into readable code.
  6. Simplify complex calculations that are not necessary for your report.

In conclusion, following best practices in DAX such as reusing measures, using descriptive names and descriptions, and using code formatter can lead to more efficient and understandable code, and ultimately, better reports and analysis.

Supportscreen tag