When to use variables vs measures

beanboy — Mon, 16 Dec 2024 22:12:15 GMT

Hi everyone, came across some DAX behavior I could use some clarification on. I have some pseudo-code below, but basically I am taking a unique Customers table and counting the rows that meet two conditions (based on the related Sales fact table).

What is stumping me is trying to aggregate this at a State level, for example. If I have the code use the VARIABLES in the return statement, it does not aggregate at a State level. It still outputs correctly in a table of Customers, but does not aggregate. However, if I instead copy each variable to a separate measure, and then switch the DAX to reference the MEASURES, the measure now correctly aggregates at a state level.

I know this for whatever reason is working as intented, so my question is why does it behave this way?

Also, while I'm here, is my syntax for COUNTROWS using a FILTER statement correct, or could it be done more efficiently?

#NewCustomers = VAR __PriorMonthStart = EOMONTH ( TODAY(), -2 ) +1 VAR __VARIABLENumSalesAllPrior = CALCULATE ( [NumSales], 'SalesData'[DateReceived] < __PriorMonthStart ) VAR __VARIABLENumSalesMonthPrior = CALCULATE ( [NumSales], DATESINPERIOD ( 'Calendar'[Date], __PriorMonthStart, 1, MONTH ) ) RETURN CALCULATE ( COUNTROWS ( 'Customers(unique)' ), FILTER ( 'Customers(unique)', AND ( [MEASURENumSalesAllPrior] = 0, [MEASURENumSalesMonthPrior] > 0 ) ) )

Re: When to use variables vs measures

lbendlin — Mon, 16 Dec 2024 22:31:17 GMT

Measures are dynamic, they recalculate for each different filter context. You may not always want that, so you materialize intermediate results into variables (which is actually a misnomer as for all intents and purposes these are constants, not variables) so the values are no longer impacted by subsequent measure calculations.

You can materialize intermediate steps into scalar or table variables but the final result of the measure always must be a scalar value.

Re: When to use variables vs measures

beanboy — Mon, 16 Dec 2024 22:37:20 GMT

Thank you. I understand conceptually what you mean, still trying to master it though.

topic Re: When to use variables vs measures in DAX Commands and Tips

When to use variables vs measures

Re: When to use variables vs measures

Re: When to use variables vs measures