DAX Behavior

[EfratY]

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

Average Retail Price =
AVERAGE(
'Product Lookup'[ProductPrice]
)

Overall Average Price =
CALCULATE(
[Average Retail Price],
ALL('Product Lookup')
)

High Ticket Orders =
CALCULATE(
[Total Orders],
FILTER(
'Product Lookup',
'Product Lookup'[ProductPrice] > [Average Retail Price]
)
)

When I use [Overall Average Price] inside the High Ticket Orders measure (instead of [Average Retail Price]), the measure works as expected.

However, when I use [Average Retail Price], the High Ticket Orders measure returns blank in a Matrix visual — even though the Matrix has no rows or columns (only the measure in Values).

My question is:

Why does using [Average Retail Price] cause the measure to return blank, while using [Overall Average Price] works correctly?

What is happening in terms of filter context that explains this behavior?

Thank you in advance for your help!

[mizan2390]

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.

[v-bmanikante]

Hi @EfratY ,

May I check if this issue has been resolved? If not, Please feel free to contact us if you have any further questions.

Thank you

[v-bmanikante]

Thankyou @OwenAuger and @pcoley  for your responses.

Hi @EfratY 

We appreciate your inquiry through the Microsoft Fabric Community Forum.

We would like to inquire whether have you got the chance to check the solutions provided by @OwenAuger   and @pcoley to resolve the issue. We hope the information provided helps to clear the query. Should you have any further queries, kindly feel free to contact the Microsoft Fabric community.

Thank you.

[OwenAuger · Accepted solution]

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/

[pcoley]

@EfratY 

The key difference comes down to filter context (and how row context from the FILTER iterator interacts with it) during evaluation of the condition inside High Ticket Orders.

Your High Ticket Orders measure builds a filtered version of the 'Product Lookup' table and then uses that as a table filter argument inside CALCULATE. The FILTER function iterates row-by-row over 'Product Lookup' (in whatever outer filter context exists—in your empty Matrix visual, that's the full/unfiltered model). For each row, it evaluates the boolean condition:

'Product Lookup'[ProductPrice] > [Some Average Measure]

• The left side ([ProductPrice]) is a direct column reference → evaluated in row context (the current product's price).
• The right side is a measure reference → measures always evaluate in filter context (never directly in row context).

Here's where the behavior diverges:

When you use [Average Retail Price] (the one that returns blank)

• [Average Retail Price] = AVERAGE('Product Lookup'[ProductPrice]) is a simple aggregator with no CALCULATE or ALL.
• During the row-by-row iteration of FILTER('Product Lookup', ...), the evaluation of this measure ends up seeing a filter context that is effectively restricted to the current single row being iterated (this is a common DAX "gotcha" when the measure aggregates the exact same table the iterator is scanning, even though row context doesn't normally auto-transition to filter context).
• Result: For every product row, [Average Retail Price] evaluates to that row's own ProductPrice (i.e. the average of one value = the value itself).
• The condition becomes ProductPrice > ProductPrice → always false.
• FILTER therefore returns an empty table.
• CALCULATE([Total Orders], <empty table filter on Product Lookup>) propagates no products → no related sales rows → DISTINCTCOUNT returns blank.

This happens even in your empty Matrix (grand-total context with no slicers/row/column fields), because the per-row evaluation inside FILTER is what introduces the restrictive context.

When you use [Overall Average Price] (the one that works)

• [Overall Average Price] = CALCULATE([Average Retail Price], ALL('Product Lookup')) explicitly removes every filter on the 'Product Lookup' table (via ALL + the outer CALCULATE).
• Even if the FILTER iterator introduces any row-specific filtering during predicate evaluation, the ALL wipes it out.
• Result: The measure always returns the true grand-total average across all products (constant scalar, same for every row in the iteration).
• The condition correctly identifies products where ProductPrice > grand average.
• FILTER returns the proper subset of high-price product rows.
• CALCULATE([Total Orders], <that filtered table>) correctly counts the distinct orders linked to those products → shows the expected number.

Why the empty Matrix still shows this difference

The grand-total context (no dimensions) means the outer filter context is the same for both versions. But the internal evaluation inside FILTER(...) is what matters—and that's where the simple aggregator vs. the CALCULATE(... ALL ...) version behaves differently.

Recommended fix / best practice

Don't reference the measure directly in the FILTER predicate when it aggregates the iterated table. Instead, capture the scalar value once (outside the iterator) with a variable:

High Ticket Orders =
VAR AvgPrice = [Average Retail Price]   // or [Overall Average Price] — either works now
RETURN
    CALCULATE(
        [Total Orders],
        FILTER(
            'Product Lookup',
            'Product Lookup'[ProductPrice] > AvgPrice
        )
    )

(You could also hard-code the comparison with CALCULATE([Average Retail Price], ALL('Product Lookup')) directly in the VAR, or use AVERAGEX(ALL('Product Lookup'), 'Product Lookup'[ProductPrice]).)

I hope this helps. if so please mark it as a solution. kudos are welcome.