Forum Discussion

EfratY's avatar
EfratY
New Member
5 months ago
Solved

DAX Behavior

  Hi everyone, I’m trying to understand a behavior in DAX and would appreciate your help. Here are my measures:   Total Orders = DISTINCTCOUNT( 'Sales Data'[OrderNumber] ) Aver...
  • OwenAuger's avatar
    OwenAuger
    5 months ago

    HI EfratY 

    Here are the main points explaining what's going on:

    1. Any measure reference in DAX is automatically wrapped in a hidden CALCULATE.
      In this case, [Average Retail Price] is treated as CALCULATE ( [Average Retail Price] ).
    2. Calling the CALCULATE function within a row context causes context transition, where the row context is transformed into an equivalent filter context before evaluating the first argument of CALCULATE.
    3. In the High Ticket Orders measure, the FILTER function is an iterator which iterates over 'Product Lookup', evaluating the 2nd argument expression Product Lookup'[ProductPrice] > [Average Retail Price] within a row context corresponding to each row of 'Product Lookup'.
    4. Due to this context transition, the values of all columns of 'Product Lookup' in the "current row", including 'Product Lookup'[ProductPrice], are added as filters when evaluating [Average Retail Price]. This means [Average Retail Price] will return the average of just the single price on the current row iterated by FILTER. In other words [Average Retail Price] will return the same value as 'Product Lookup'[ProductPrice] for any given row of 'Product Lookup' iterated by FILTER.
    5. As a result, 'Product Lookup'[ProductPrice] > [Average Retail Price] is logically equivalent to 'Product Lookup'[ProductPrice] > 'Product Lookup'[ProductPrice], which is always false (a value is never greater than itself).
    6. If you replace [Average Retail Price] with [Overall Average Price], context transition still happens, but [Overall Average Price] removes filters on 'Product Lookup' via ALL ( 'Product Lookup' ) so the filters due to context transition are ignored. The value returned by [Overall Average Price] would however ignore any existing filters on 'Product Lookup' so would not change due to any existing filters on 'Product Lookup'.
    7. Suggested solution: I would generally recommend storing the value of [Average Retail Price] in a variable before calling FILTER. This avoids the whole context transition complication and should return the intended value. I would also recommend changing FILTER ( ... ) to a boolean condition within KEEPFILTERS. See below:
    High Ticket Orders improved v1 =
VAR AveragePrice = [Average Retail Price]
RETURN
    CALCULATE (
        [Total Orders],
        FILTER (
            'Product Lookup',
            'Product Lookup'[ProductPrice] > AveragePrice
        )
    )
    High Ticket Orders improved v2 =
VAR AveragePrice = [Average Retail Price]
RETURN
    CALCULATE (
        [Total Orders],
        KEEPFILTERS ( 'Product Lookup'[ProductPrice] > AveragePrice )
    )

     

    Some good reading on this topic:

    https://www.sqlbi.com/articles/understanding-context-transition-in-dax/

     

     

mizan2390's avatar
mizan2390
Super User
5 months ago
The behavior you are experiencing is a classic example of how row contexts, measure references, and context transition interact in DAX.
Here is exactly what is happening:
The FILTER function is an iterator. When you write FILTER('Product Lookup', ...), DAX scans the 'Product Lookup' table row by row, creating a row context for the evaluation of the condition.
When you place a measure reference like [Average Retail Price] inside this row context, DAX automatically wraps it in an implicit CALCULATE function. If CALCULATE is executed within a row context, it triggers a context transition. This means DAX invalidates the row context and automatically creates a new filter context that filters the model down to the currently iterated row.
As a result, [Average Retail Price] does not compute the grand total average of all products. Instead, because of the context transition, it computes the average retail price only for the current product being iterated.
Consequently, your logical test evaluates if the product's price is strictly greater than its own price. Because there is no product whose price is greater than itself, the condition is never met, and FILTER returns an empty table. Passing an empty table into the outer CALCULATE to compute [Total Orders] naturally yields a BLANK.
Your [Overall Average Price] measure is explicitly defined with the ALL('Product Lookup') modifier.
When you use [Overall Average Price] inside FILTER, the implicit CALCULATE still triggers a context transition that attempts to filter the calculation down to the current row. However, CALCULATE executes its operations in a very specific order: CALCULATE modifiers (like ALL) are applied after the context transition happens.
Because ALL is evaluated after the context transition, it acts as a filter remover and overrides the effect of the context transition. It removes the row-level filters applied to the 'Product Lookup' table, allowing the measure to successfully compute the global average across all products.
How to Fix This Best
While using your [Overall Average Price] measure works, relying on ALL inside the loop causes DAX to repeatedly calculate the global average for every single row, which is inefficient. The best practice is to compute the global average once by storing it in a variable before the FILTER iteration begins.
Because variables are constants that are evaluated in the scope where they are defined, they ignore any row contexts created later in the code.
Here is the optimal DAX pattern for your High Ticket Orders measure:
High Ticket Orders = 
VAR GlobalAvgPrice = [Average Retail Price]
VAR HighTicketProducts = 
    FILTER(
        'Product Lookup',
        'Product Lookup'[ProductPrice] > GlobalAvgPrice
    )
RETURN
    CALCULATE(
        [Total Orders],
        HighTicketProducts
    )

if this solves your problem, please mark this as solved.