about evaluation context

[qqqube]

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])
    )
)

[dharmendars007]

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

LinkedIN 

[qqqube · Accepted solution]

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.