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:
DEFINE VAR MyMonth = "January" VAR FilteredMonths = FILTER ( VALUES ( Sales[Month] ), Sales[Month] = MyMonth ) EVALUATE CALCULATETABLE ( Sales, FilteredMonths )
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:
DEFINE VAR MyMonth = "January" VAR FilteredMonths = FILTER ( VALUES ( Sales[Month] ), Sales[Month] = MyMonth ) MEASURE Sales[FirstMeasure] = COUNTROWS ( FilteredMonths ) EVALUATE ROW ( "First Measure", [FirstMeasure] )
…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:
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) )
…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.