Variables & Comments in DAX Power BI

In this article, we learn about using variables and Comments when writing DAX in Power BI.

VARIABLES IN DAX

Your DAX code can be made simpler with VARIABLES, which can also aid in performance improvement and debugging.

To use variables in your DAX measure you need to declare a variable using the VAR keyword, give the variable a name, and then assign an expression to the variable. You can use multiple variables in a measure but when using variables you must use a RETURN statement to specify the final output to be returned.

Using variables in your DAX formulas can help in the following ways:

• Improve Performance
• Improve Readability
• Reduce Complexity

A DAX variable is indeed a constant, meaning that it is a name for the value computed by the definition of the variable. Each set of VAR / RETURN can include multiple variables, you can have any number of VAR for one RETURN. Note that variables should be named using one word; variables can’t contain spaces. We can use underscores instead. Also, each variable name should be unique.

Creating Measures with Variables =
VAR <name1> =
    <expression1>
VAR <name2> =
    <expression2>
. . .
RETURN
    <result_expression1>

This simple formula includes the definition of two variables:

Sales Per Customer =
VAR SalesAmount =
    SUMX(Orders,
        Orders[Quantity] * Orders[Price]
    )
VAR NumCustomer =
    DISTINCTCOUNT (Orders[Customer ID])
RETURN
    DIVIDE(SalesAmount, NumCustomer)

The SalesAmount variable contains the amount of sales computed through SUMX, whereas the NumCustomer variable contains the number of customers, as the DISTINCTCOUNT of Orders[Customer ID].

The two variables are used in the RETURN part, where we divide one by the other. The result of the entire expression is the RETURN part.

Another of DAX function that use variable to get a more neater code

Total Sales YoY Growth % =
VAR TotalSales =
    SUM(Orders[Revenue])
VAR TotalSalesPP =
    CALCULATE(
        SUM(Orders[Revenue]),
        PARALLELPERIOD('Calendar'[Date], -12, MONTH)
)
RETURN
    DIVIDE(TotalSales - TotalSalesPP, TotalSalesPP)

To simplify the code further, we can define a new variable called Variance, and a final variable called Result to store the final calculation to be returned. The final code then becomes:

Total Sales YoY Growth % =
VAR TotalSales = SUM(Orders[Revenue])
VAR TotalSalesPP =
    CALCULATE(
        SUM(Orders[Revenue]),
        PARALLELPERIOD('Calendar'[Date], -12, MONTH)
    )
VAR Variance = TotalSales – TotalSalesPP
VAR Result = DIVIDE(Variance, TotalSalesPP)
RETURN
    Result

COMMENTS IN DAX

Adding brief comments to the DAX code is another technique to make calculations or measurements easier to understand. This is an excellent method to make the code simpler to comprehend so that other team members are aware of its purpose. Enhancing user experience by adding comments is effective.

Comments can be placed anywhere in the formula box. We only need to type two forward slashes (//) at the beginning of the sentence or statement to make it a comment.

We can also the keyboard shortcut Ctrl + KC and Ctrl + KU to add comment and remove comment respectively from a DAX expression.

• Just select line(s) of code you want to comment
• Press Crtl + KC to comment those lines
• Press Ctrl + KU to uncomment commented lines

This simple formula includes the definition of two comments:

Total Sales YoY Growth % =
VAR TotalSales =
    SUM(Orders[Revenue])   // This calculate Total Sales
VAR TotalSalesPP =
    CALCULATE(
        SUM(Orders[Revenue]),   // This cal Total Sales last year
        PARALLELPERIOD('Calendar'[Date], -12, MONTH)
    )
RETURN
    DIVIDE(TotalSales - TotalSalesPP, TotalSalesPP)

This won’t, in any way, affect how the measure or formula works. However, we need to make sure to keep comments concise.

If you want to learn more about Power BI reach out
@2347062428901, ridohconceptsonline@gmail.com.