The importance of using DAX variables / the Kusto/ADX edition
Published Nov 30 2022 04:55 AM 1,131 Views
Microsoft

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!

Co-Authors
Version history
Last update:
‎Nov 30 2022 04:55 AM
Updated by: