Using variables

While DAX functions can be nearly infinitely nested, using variables can avoid doing the same calculation multiple times and also improves overall code readability. Using variables can help you break complex calculations down into smaller, more consumable pieces that can be individually verified step by step.

Getting ready

To prepare for this recipe, perform the following steps:

1. Create a table using the following formula:

R3_Table = GENERATESERIES(1,30,1)

2. Create a column in that table using the following formula:

Column = ROUNDUP('R3_Table'[Value]/11,0)

How to do it...

Variables are created by using the VAR statement with the following syntax:

VAR <name> = <expression>

<name> is the name of the variable. Variable names must begin with the letters a-z or A-Z. The only other supported characters within variable names are the characters 0-9. Existing table names and certain keywords are not permitted as variable names.

DAX calculations that use a VAR statement must also use a RETURN statement.

By following these principles, we can write a DAX measure as follows:

Variable DAX = 
/*
 * This measure summarizes the table, Table, grouping by [Column] and summing [Column1]. This  
 * summarized table is then filtered to values 2 and 3 in [Column] and then sums up [Column1]
 *
 * Gregory J. Deckler
 * [email protected]
 * 10/7/2019
 */
VAR __summarizedTable =                 // Summarize table by [Column], summing [Value]
    SUMMARIZE(
        'R03_Table',
        'R03_Table'[Column],
        "__Value",
        SUM( 'R03_Table'[Value] )
    )
VAR __filteredTable =                   // Filter summarized table for 2 and 3
    FILTER(
         __summarizedTable,             // Here we use our __summarizedTable variable
        [Column] = 2
        ||
        [Column] = 3
    )
VAR __sum =                             // Sum [__Value]
    SUMX(
        __filteredTable,                // Here we use our __filteredTable variable
        [__Value]
    )
RETURN                                  // If result is < 400, return the sum, otherwise -1
    IF(
        __sum < 400,                    // We avoid having to do the same calculation twice
        __sum,
        -1
    )

How it works...

The code creates three variables, __summarizedTable, __filteredTable, and __sum. The __summarizedTable variable creates a table in memory using the SUMMARIZE function to return a table summarized by [Column] and by summing the [Value] column. This variable takes on the value of the table as shown:

Column	__Value
1	66
2	187
3	212

The __filteredTable variable uses the FILTER function to filter the table represented by the __summarizedTable variable to just the values 2 and 3 in the [Column] column. This variable takes on the value of the table as shown:

Column	__Value
2	187
3	212

The __sum variable uses the SUMX function to sum the [__Value] column of the two remaining rows in the table, taking on a value of 187 + 212, or 399.

The RETURN statement uses an IF statement. This IF statement checks to see whether the __sum variable is less than 400. If the variable is less than 400, the value stored in the __sum variable is returned. Otherwise, the value -1 is returned.

Variables can only be referenced within the DAX calculation in which they are created. As shown in both __filteredTable and __sum, variables can refer to previously created variables within the same DAX calculation.

There's more...

You can nest variables. For example, this recipe could be written as follows:

Nested Variable DAX = 
/*
 * This measure summarizes the table, Table, grouping by [Column] and summing [Column1]. This  
 * summarized table is then filtered to values 2 and 3 in [Column] and then sums up [Column1]
 *
 * Gregory J. Deckler
 * [email protected]
 * 10/7/2019
 */
VAR __sum =                                        // Sum [__Value]
    SUMX(
        VAR __filteredTable =      
            FILTER(                                // Filter summarized table for 2 and 3
                VAR __summarizedTable =            // Summarize table by [Column], summing [Value]
                    SUMMARIZE(
                        'R03_Table',
                        'R03_Table'[Column],
                        "__Value",
                        SUM( 'R03_Table'[Value] )
                    )
                RETURN __summarizedTable           // Return the summarized table
                ,            
                [Column] = 2
                ||
                [Column] = 3
            )
        RETURN __filteredTable                     // Return the filtered table
        ,
        [__Value]
    )
RETURN                                             // If result is < 400, return the sum, otherwise -1
    IF(
        __sum < 400,
        __sum,
        -1
    )

See also

For more details regarding this recipe, refer to the following links:

• VAR: https://docs.microsoft.com/en-us/dax/var-dax
• Using variables in Power BI – a detailed example: https://blog.enterprisedna.co/2019/09/09/using-variables-in-dax-a-detailed-example/
• Using DAX variables in iterators: https://www.kasperonbi.com/using-dax-variables-in-iterators/
• Using variables within DAX: https://powerbi.tips/2017/05/using-variables-within-dax/
• Variables in DAX: https://www.sqlbi.com/articles/variables-in-dax/
• Using variables in DAX: https://exceleratorbi.com.au/using-variables-dax/
• Power BI Quick Measure, Runge Kutta: https://community.powerbi.com/t5/Quick-Measures-Gallery/Runge-Kutta/m-p/411280