Article

DAX Language - Variables in DAX


A-Badde | Posted on April 10, 20

Variables in DAX

  The VAR keyword stores the result of an expression as a named variable, which can then be passed as an argument to other measure expressions.

  VAR keyword returns a variable containing the result of the expression argument.

 Syntax:

VAR <VARNAME1> = <EXPRESSION1>
VAR <VARNAME2> = <EXPRESSION2> [...]]
RETURN <RESULT_EXPRESSION>
  • Description of the parameter:

 

PARAMETER

DESCRIPTION

varname

The name of your variable.

Supported character set: a-z, A-Z, 0-9.

Delimiters are not supported

0-9 are not valid as first character

expression

DAX expression which returns a scalar or table value

 

 Example:

  We want to calculate the percentage of year-over-year growth using the formula:

SalesAmount YoY% = ([Sum of SalesAmount] – [SalesAmount PreviousYear]) / [Sum of SalesAmount]

We could create three different measures ([Sum of SalesAmount], [SalesAmount PreviousYear] et [Sum of SalesAmount]) and then apply the previous formula to get the final result.

By using variables, you can get the same outcome, but in a more readable way. In addition, the result of the expression doesn’t have to be recalculated each time it is used.

The following formula uses the variables to calculate the percentage of year-over-year growth.

SalesAmount YoY% = 
            var SalesAmount = SUM(Sales[SalesAmount])
            var SalesAmountLastYear = CALCULATE( SUM(Sales[SalesAmount]),
                                        SAMEPERIODLASTYEAR('Calendar'[Date])
                                        )
            return
            IF(SalesAmount,
                DIVIDE(SalesAmount - SalesAmountLastYear, SalesAmount)
            )

 

Note that:

  An expression passed as an argument to VAR can contain another VAR declaration.

  Variables can refer to measures.

  Variables can refer to previously defined variables.

 

Related Video:

 

Related Articles:

 


(0) Comments

There is no comment

Leave a Comment

Your email address will not be published. Required fields are marked *

Add to favorite
Categories
Recent Posts
DAX Language - CONTAINS Function
A-Badde | May 09, 20

The CONTAINS function returns a boolean value, TRUE if each ...Read More


DAX Language - RANKX Function
A-Badde | May 05, 20

The RANKX function returns a single integer value that repre...Read More


DAX Language - AND and OR Functions
A-Badde | April 10, 20

AND/OR functions check whether all arguments are TRUE or one...Read More


DAX Language - Variables in DAX
A-Badde | April 10, 20

Use Var keyword to store the result of an expression as a na...Read More