[DAX] Why and how to use variables

Example Case

First of all, the variables store the result of an expression with a name that can be used later as an argument for other operations inside the measures. Once the value is calculated the operation in the variable, the values won’t change when we use them later even if we use them inside of a CALCULATE or any other function. What does this mean? what happen when we create a variable? how are those variables evaluated in our code? The variables are evaluated once in the code or calculation for each context. This means that the result of the measure won’t change if we manipulate the filter context in the expression later in the code.

In order to explain this better, let’s check the following example.


The dark grey line is the new measure.


Let’s assume we have a “projection” of quantity measure. I know that’s a terrible way to calculate a projection, but it works for our example. In the Line Chart we can see the projection of quantity breakdown in months for a filtered year. As you can see the previous picture of the measure, it has repeated code. SUM(InternetSales[Quantity]) is three times.

Due to the fact that we know variables are good, we want to use them. We think that we need to avoid repeating code, so we add that function to a variable called “__cantidad”:


Now we might be thinking the code looks amazing and we have made it better because it will calculate the quantity once. However, we haven’t thought about how variables work or how they are evaluated. The resault of the measure would be something like this (blue line):



If we stop for one minute to check the measure after reading the previous definition we might find the mistake. The variable __cantidad is executed once for each month at the beginning of the evaluation, then it won’t be affected in the same way with ALLEXCEPT that is trying to manipulate the context. That’s why the line is so different from the one without variables. When the denominator is executed, we already have a value calculated and the denominator won’t try to get the annual value like we were expecting.

If we want to use variables to evaluate the denominator context, then we need to do the whole calculation in a variable or don’t use variables at all to make it work. The SUM inside the CALCULATE needs its own context to run the ALLEXCEPT correctly. So we are creating a second variable to evaluate that denominator:


Writing the measure this way we are getting the correct result matching the measure without variables:


Thanks to variables we can appreciate the code in a cleaner way. Before we had to read it all to understand what it is doing. Now just reading the RETURN we have a small understanding of the calculation. Having the measures split in two would help us debugging each one value too. How? just adding a single variable in the RETURN to check and change it back again until we find the values we are expecting in the math. We would be able to find an error faster if the number is wrong or blank.

Benefits of variables

After learning how variables are evaluated, we can talk about benefits. The correct implementation of variables can improve performance, reliability, readability and complexity reduction.

  1. Performance: executing repeated blocks of code once make the execution fater.
  2. Reliability: distributing the code in variables help us debuging because of the possibility of returning single variables to understand the results of our formulas.
  3. Readability: a clean code is easier to maintain and read. We can get a quick easy look faster.
  4. Complexity Reduction: reducing and making the code cleaner will help us make it easier to understand it and get the sense of the evaluation.

I hope you have enjoyed the post and this helps you write better DAX in the future.

And you…. are you coding with variables to improve the performance or debug?


Source link

Be the first to comment

Leave a Reply

Your email address will not be published.