topic about evaluation context in DAX Commands and Tips

about evaluation context

qqqube — Wed, 11 Sep 2024 07:54:54 GMT

there is an example in Microsoft training modules 2-aggregation-iterator-functions . and in the higher grain summarization part, it gives a formula:

Revenue Avg Order = AVERAGEX( VALUES('Sales Order'[Sales Order]), [Revenue] )

where [Revenue] is:

Revenue = SUMX( Sales, Sales[Order Quantity] * Sales[Unit Price] * (1 - Sales[Unit Price Discount Pct]) )

lets put them together:

Revenue Avg Order = AVERAGEX( VALUES('Sales Order'[Sales Order]), SUMX( Sales, Sales[Order Quantity] * Sales[Unit Price] * (1 - Sales[Unit Price Discount Pct]) ) )

my question is:

is the formula above the same as this one? why?

(I test these two formulas and find them return the same result in my data model. )

Revenue Avg Order2 = AVERAGEX ( VALUES('Sales Order'[Sales Order]), SUMX ( FILTER(Sales, Sales[Sales Order] = EARLIER('Sales Order'[Sales Order])), Sales[Order Quantity] * Sales[Unit Price] * (1 - Sales[Unit Price Discount Pct]) ) )

Re: about evaluation context

qqqube — Wed, 11 Sep 2024 08:26:46 GMT

chatgpt gives an answer as follows:

Execute SUMX

For each Sales Order, AVERAGEX passes the current Sales Order as the row context and evaluates the SUMX function.
SUMX(Sales, ...) iterates over the Sales table. However, because DAX applies the current row context automatically, the SUMX will only process rows in the Sales table that match the current Sales Order.
This is due to DAX’s context propagation, where the outer row context (in this case, the current Sales Order) is passed into the inner calculation, so SUMX operates only on rows that match the Sales Order.

Re: about evaluation context

dharmendars007 — Wed, 11 Sep 2024 10:51:02 GMT

Hello qqqube ,

Yes, Both formulas will give the same result but DAX automatically handles the filter context efficiently in Formula 1..

If you find this helpful , please mark it as solution and Your Kudos are much appreciated!

Thank You

Dharmendar S