Variables are the best thing to happen to DAX since, well forever – they are so cool I’m almost ready to like DAX as much as I like MDX. There are already several good articles and blog posts out there describing how to use them (see here and here), but I was looking at a Profiler trace the other day and saw something I hadn’t yet realised about them: you can declare and use variables in the DEFINE clause of a DAX query. Since my series of posts on DAX queries still gets a fair amount of traffic, I thought it would be worth writing a brief post showing how this works.
Say you have the following table (called Sales) in your model:
You can declare DAX variables in the DEFINE clause of a query like so:
[sourcecode language=”text” padlinenumbers=”true”]
DEFINE
VAR MyMonth = "January"
VAR FilteredMonths =
FILTER ( VALUES ( Sales[Month] ), Sales[Month] = MyMonth )
EVALUATE
CALCULATETABLE ( Sales, FilteredMonths )
[/sourcecode]
This query returns the following result:
The benefits of using variables in this way are the same as you get when using variables in measures and calculated columns: improved readability, less repetition and potential performance improvements.
I also wondered whether I would be able to refer to these variables inside measures declared in the DEFINE clause, but unfortunately you can’t. The following query:
[sourcecode language=”text” padlinenumbers=”true”]
DEFINE
VAR MyMonth = "January"
VAR FilteredMonths =
FILTER ( VALUES ( Sales[Month] ), Sales[Month] = MyMonth )
MEASURE Sales[FirstMeasure] =
COUNTROWS ( FilteredMonths )
EVALUATE
ROW ( "First Measure", [FirstMeasure] )
[/sourcecode]
…returns the error
“Failed to resolve name ‘FilteredMonths’. It is not a valid table, variable or function name”.
However if you define your calculations inside the query itself, for example using the Summarize() or AddColumns() functions, or like so:
[sourcecode language=”text”]
DEFINE
VAR MyMonth = "January"
VAR FilteredMonths =
FILTER ( VALUES ( Sales[Month] ), Sales[Month] = MyMonth )
EVALUATE
ROW (
"First Calc", COUNTROWS (FilteredMonths),
"Second Calc", CALCULATE (SUM(Sales[Sales]), FilteredMonths)
)
[/sourcecode]
…the query works:
In a lot of cases, multiple calculations contain some of the same logic and being able to use variables to share tables and values between calculations opens up some really interesting opportunities for performance optimisations.
Chris, I don’t like the VAR definition in DEFINE as you described, but I don’t see this as a big limitation considering you can write:
EVALUATE
VAR a = 1
VAR b = a + 1
RETURN ROW ( “x”, b )
Using VAR/RETURN you can insert a variable in any expression, also in table expression, and the variable itself can be a table expression.
EVALUATE
VAR a = 1
VAR b = a + 1
VAR c = ROW ( “x”, b )
RETURN c
Do you see a reason why the VAR in DEFINE would be really useful?
Thanks!
Good question – I suppose it’s a matter of coding style. To me it seems a bit cleaner to separate the variables from the EVALUATE statement. Power BI Desktop uses variables declared in the DEFINE clause so maybe there’s something we don’t know…?
Something to discuss at next conferences meeting MS people 🙂
I found a reason! Whatever you define before EVALUATE can be used in following EVALUATE statements within the same batch.
I described this here: http://sqlblog.com/blogs/marco_russo/archive/2016/10/20/analyze-multiple-evaluate-statements-in-a-single-dax-statements-in-dax-studio.aspx
Hi Chris. Great post! Variables are indeed a really great addition to DAX, but what I really miss is a way of sharing a table-valued variable across multiple measures in a Tabular Model. This would make it so much less of a hassle to build Time Intelligence and other complicated logic (which we would use MDX SCOPE statements for in Multidimensional). Someone already posted this idea: https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/14950908-add-global-variables-to-dax
What do you think?
I agree, I would love to see that. I think the dev team are already aware of the need so hopefully we’ll get it someday.