DAX - Understanding Var

[Stivik1981]

Hi Friends,

Newbie to DAX here and I'm trying to get my head around something:

I have a calculated column that I was experimenting with.  The formula is as follows:

var x = trxs[ImportDayNum] return COUNTROWS(FILTER(ALL(trxs), trxs[ImportDayNum] =x))

Without getting into the weeds of it this formula actually works as intended.  My question is this:

If I remove the Var, and simply code it this way:

COUNTROWS(FILTER(ALL(trxs), trxs[ImportDayNum] =trxs[ImportDayNum])) -->  It sends back a whacky result!

I'm totally confused!

If the var IS the same thing as the long-coded form, why would I get two results?

Please help!

[Anonymous · Accepted solution]

Thanks for the reply from freginier  and Jihwan_Kim , please allow me to provide another insight:

Hi, @Stivik1981 
Thanks for reaching out to the Microsoft fabric community forum.

Yes, as freginier  and Jihwan_Kim mentioned, when you use var x = trxs[ImportDayNum], you create a variable x that holds the ImportDayNum value of the current row. Then, you use this variable in the FILTER() function to compare all rows' ImportDayNum values to x. This means you are comparing all rows' ImportDayNum values to the ImportDayNum value of the current row.

var x = trxs[ImportDayNum]
return COUNTROWS(FILTER(ALL(trxs), trxs[ImportDayNum] = x))

This formula calculates the number of rows that have the same ImportDayNum value as the current row.

However, when you remove var and directly use trxs[ImportDayNum], the formula becomes:

COUNTROWS(FILTER(ALL(trxs), trxs[ImportDayNum] = trxs[ImportDayNum]))

In this case, trxs[ImportDayNum] = trxs[ImportDayNum] is TRUE for every row because each row's ImportDayNum value is always equal to itself. This means the FILTER() function does not filter out any rows, resulting in the count of all rows in the table.

Therefore, using var to create the variable x is crucial as it ensures that you are comparing the ImportDayNum value of the current row in the FILTER() function, rather than the ImportDayNum value of each row. Besides its use in the FILTER function, using var also improves code readability, aesthetics, and facilitates reuse.

Here are the relevant document screenshots:

For more details, please refer to:

Use variables to improve your DAX formulas - DAX | Microsoft Learn

VAR keyword (DAX) - DAX | Microsoft Learn

Of course, if you have any new discoveries or questions, please feel free to get in touch with us.
 

Best Regards,

Leroy Lu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[freginier]

Hi there! 

Interesting question.. I believe this is the answer: 

• Without VAR: DAX compares each row’s ImportDayNum to itself, potentially leading to unintended evaluations or context mismatches.
• With VAR: The stored value ensures consistency in the filter operation.

Hope this makes sense and that you understand a bit better.

Cheers!

Zoe 😁😁

[Jihwan_Kim]

Hi,

I think, if you want to write without VAR, please try using EARLIER DAX function.

EARLIER function (DAX) - DAX | Microsoft Learn

"Returns the current value of the specified column in an outer evaluation pass of the mentioned column.

EARLIER is useful for nested calculations where you want to use a certain value as an input and produce calculations based on that input. In Microsoft Excel, you can do such calculations only within the context of the current row; however, in DAX you can store the value of the input and then make calculation using data from the entire table.

EARLIER is mostly used in the context of calculated columns."

calculated column CC =
COUNTROWS (
    FILTER ( trxs, trxs[ImportDayNum] = EARLIER ( trxs[ImportDayNum] ) )
)