This post has been republished via RSS; it originally appeared at: Azure Data Explorer Blog articles.
The importance of using DAX variables
the Kusto/ADX edition
Why use variables – the vanilla version
You probably heard about DAX variables and saw recommendations to use variables when writing DAX measures.
There are two main reasons for using variables:
- Readability of the DAX syntax
- Performance
Readability also includes being able to debug a measure by returning the different variables to make sure that they contain the right values.
Performance is about calculating expressions once instead of many times.
If you use a measure or an expression more than once or calculate a table more than once, it is a good idea to store the value of the measure or table in a variable so it will be calculated only once
So instead of Margin=IF(ISBLANK([Sales]),BLANK(),[Sales]-[Cost])
We can write
Margin =
Var S=[Sales]
Var C={Cost]
Var B=ISBLANK(S)
Var R=if(B,BLANK(),S-C)
Return R
Could be written with less variables but you can see the point.
[Sales] is calculated only once and you can return each variable at a time to debug the logic.
Why use variables – Kusto (Direct Query) edition
The performance aspect of not repeating the calculation of a measure becomes much more important when you use Direct Query.
A DAX measure in most cases takes a very short time, typically a few milliseconds.
The same measure when using direct query will send a separate query for each use of the expression.
The query can potentially scan billions of rows and take seconds.
Running one instead 2 or 3 queries can make a significant difference.
In the attached example one table is using a simple sum, the second uses a measure that calculates Sales three times and the third table use a variable.
Refreshing the visuals with performance analyzer on, you can see the difference between the three tables
If you look at the queries using .show queriers, you’ll note that there is a weird looking query
["SalesTable"]
| summarize by ["ColorName"]
| limit 1000001
I’ll dedicate another article to explain this query so stay tuned!