local variable or a separate measure ?

[Med_Aziz]

Hello Power BI Community.

There's one thing I don't understand in Dax:

when I write a Dax measure as follows:

measure =

var __sum = SUM(FactInternetSales[SalesAmount])

return

CALCULATE(__sum , LASTNONBLANK(DimDate[FullDateAlternateKey] , __sum))

==>It gives me the wrong value.

But when I write the formula of the local variable in a separate measure like this:

sum measure = SUM(FactInternetSales[SalesAmount])

and then I insert this measure in the other like this:

measure = CALCULATE([sum measure] , LASTNONBLANK(DimDate[FullDateAlternateKey] , [sum measure]))

==>It works and it gives me the right value

Why ?

Here is the data model :

(I work with the adventureworksDW database)

[Med_Aziz]

Hello,

In the official documentation, it's called "variable", i don't see why the term "variable" is a misnomer for you: Use variables to improve your DAX formulas - DAX | Microsoft Learn

I noticed that it doesn't work like in programming languages, the question is how it works and why it is different from a separate measure in my case, please read my question again.

regarding your remark about Google, it doesn't help me.

Thank you.

[Wilson_]

Hello,

Yes, I understand it's officially called a variable; the syntax is also literally VAR. It's a misnomer precisely because like you said, you noticed it doesn't work like in programming languages. (Misnomer means that the name/term is not quite accurate). 🙂

Anyway, in your specific example, wrapping the variable in a CALCULATE function doesn't do anything because the variable stores the result of a DAX expression and actually works more like a constant (hence, misnomer 🙂). Changing the filter context through a calculate cannot change the result of a variable because it is not evaluating the variable at the time of filter context manipulation.

This is supported in the link you shared from the official documentation, under the improve performance section.

Notice that the formula repeats the expression that calculates "same period last year". This formula is inefficient, as it requires Power BI to evaluate the same expression twice. The measure definition can be made more efficient by using a variable, VAR.

The following measure definition represents an improvement. It uses an expression to assign the "same period last year" result to a variable named SalesPriorYear. The variable is then used twice in the RETURN expression.

The above says using a variable saves compute time because Power BI can evaluate the expression one time and refer to it twice in the RETURN, instead of evaluating the expression every time, if the measure was written without the variable.

Additionally, in this article from SQLBI, they include this snippet:

You can think about a variable as a name for an expression. The term “variable” itself is somewhat misleading – a DAX variable cannot change its value, as the name would suggest. A DAX variable is indeed a constant, meaning that it is a name for the value computed by the definition of the variable.

Hopefully this helps. 😄

----------------------------------
If this post helps, please consider accepting it as the solution to help other members find it quickly. Also, don't forget to hit that thumbs up and subscribe! (Oh, uh, wrong platform?)

[Wilson_]

Hello Med_Aziz,

Because "variable" is a misnomer; it doesn't work the way you would expect a variable to work in a programming language.

In the future, I would suggest searching for the answer to simple questions like this on your own because you'll likely get an answer much quicker from, say, google than you would from posting on a forum. 😄

----------------------------------
If this post helps, please consider accepting it as the solution to help other members find it quickly. Also, don't forget to hit that thumbs up and subscribe! (Oh, uh, wrong platform?)