DAX - Understanding Var

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.