I have been presented here with a bizarre exploitation of VAR function. So far I have encountered VAR in the beginning of the measure, right after the equal sign. See the code below:
Expected Result = SUMX ( VALUES ( Unique_Manager[Manager] ), VAR SumBrand = CALCULATE ( SUM ( Budget_Brand[BudgetBrand] ) ) VAR SumProduct = CALCULATE ( SUM ( Budget_Product[BudgetProduct] ) ) RETURN IF ( ISBLANK ( SumProduct ), SumBrand, SumProduct ) )
Here VAR is nested deeply inside the measure code. Needless to say that this is a fantastic feature of VAR. I have been trying to use that feature in my measures with no luck. For example, this works:
Measure_good = CALCULATE( SUM(table[Amount]) )
While this does not work:
Measure_bad = CALCULATE( VAR inside_measure = SUM(table[Amount]) )
What are the rules for using VAR in this unusual way?
A VAR statement needs a matching
RETURN. You can declare several variables after one another, but then you must use a
RETURN command to pass the variables back to the main calculation, where the variable can then be used for evaluation in an IF statement or whatever
I have not tested this, but your Measure_bad should be like this (although it doesn't make much sense to use a VAR if all the remainder of the formula returns the variable)
Measure_bad = CALCULATE( VAR inside_measure = SUM(table[Amount]) RETURN inside_measure )
When you use a variable, via a VAR statement, you must always include a RETURN statement to essentially tell DAX you are going to consume those variables and return a value. In your last example, you do not include a RETURN statement and, therefore, you will get an error. In your first example, you have a RETURN statement, thus, no error. In your second example, you are not using variables.
I have found a relevant information in documentation:
An expression passed as an argument to VAR can contain another VAR declaration.
When referencing a variable:<ul><li>
Measures cannot refer to variables defined outside the measure expression, but can refer to functional scope variables defined within the expression.</li> <li>
Variables can refer to measures.</li> <li>
Variables can refer to previously defined variables.</li> <li>
Columns in table variables cannot be referenced via TableName[ColumnName] syntax.</li> </ul></blockquote>