Last week, at the SQL Server Days conference in Belgium, Kasper mentioned in his presentation that it was possible to define variables inside variables in DAX. So, for example, you could define a measure like so:
[sourcecode language='text' padlinenumbers='true']
MyMeasure =
var Outer1 =
var Inner1 = 1
var Inner2 = 2
return Inner1 + Inner2
var Outer2 = 3
return Outer1 + Outer2
[/sourcecode]
This measure returns 6 as you might expect:
There aren’t any performance benefits to doing this, although of course it helps with code readability and organisation (thanks to Marius for confirming this).
With my newly rekindled love of DAX I thought this was quite interesting. I’m not really sure why though, given that it’s not particularly useful; I think Matt might be right:
Hello:
I was reading the commentary above and I think I have similar issue to solve.
I have a business problem to solve and I was hoping I can contact you to ask if that issue is solvable? I REALLY HOPE YOU CAN HELP. I AM AT A LOSS.
I am trying to use DAX Variables to calculate recursive projections, where two measures serve as input into each other at every quarter. Her is the issue:
Measure_01 at quarter 0 is used as lagged input into Measure_02 at quarter 1
Then Measure_02 at quarter 1 is used as input into Measure_01 at quarter 1.
Then the process repeats for quarter 2 to 12.
I solved the problem partially. Essentially I am stuck on the following:
I created a variable01 from measure by using DATEADD function
Then I use that variable01 + other measures and apply DATEADD function again to create variable_02, but the DATEADD function does not seem to work when it has to shift variable_01.
My new variable_02 is calculated for the same period (i.e. not shifted to next period).
I was wondering if that is solvable, and of so how do I shift/lag my variable from variable?
Les S