Let's break down the behavior of these DAX formulas and the concept of context transitions:

Key Concepts

1. Filter Context: The set of filters applied to the data model when a calculation is evaluated.

2. Row Context: The context of a single row in a table being processed by an iterator function like FILTER.

3. Context Transition: The automatic conversion of row context to filter context when using CALCULATE.

Example 2 Analysis

Example 2 := 
CALCULATE (
    [Sales Amount],
    FILTER (
        ALL ( 'Date' ),
        'Date'[Date] <= MAX ( 'Date'[Date] )
    )
)

Why Doesn't MAX Return the Last Date in the Table?

• Row Context vs. Filter Context:

  • The FILTER function iterates over each row of ALL('Date'), creating a row context for each date.

  • However, MAX('Date'[Date]) is evaluated in the filter context of the visual, not the row context of the FILTER iteration.

  • Without a context transition (via CALCULATE), MAX remains unaware of the row context and only sees the filter context of the visual.

Visual Filter Context

• If the visual is filtered to a specific date (e.g., a slicer selects January 1), MAX('Date'[Date]) returns that specific date, not the last date in the table.

• If no filters are applied, MAX returns the last date in the entire Date table.

No Context Transition

• In Example 2, MAX does not perform a context transition. It inherits the outer filter context (from the visual), not the row context of the FILTER iteration.

Contrast with Example 1 & 3

Example 1

Example 1 := 
CALCULATE (
    [Sales Amount],
    FILTER (
        ALL ( 'Date' ),
        'Date'[Date] <= [MaxDate]
    )
)

• [MaxDate]: If [MaxDate] is a measure like MaxDate := MAX('Date'[Date]), it captures the maximum date from the visual's filter context before the FILTER iteration begins. This creates a "fixed" maximum date for all rows in FILTER.

Example 3

Example 3 := 
CALCULATE (
    [Sales Amount],
    FILTER (
        ALL ( 'Date' ),
        'Date'[Date] <= CALCULATE ( MAX ( 'Date'[Date] ) )
    )
)

• Context Transition: The inner CALCULATE around MAX forces a context transition, resetting the filter context to ALL('Date'). Thus, MAX returns the last date in the entire table.

Summary

| Example | Behavior | Context Transition | |---------|--------------------------------------------------------------------------|--------------------| | 1 | [MaxDate] captures the visual's filter context before iteration. | No | | 2 | MAX inherits the visual's filter context (no transition). | No | | 3 | Inner CALCULATE resets filter context to ALL('Date'). | Yes |

Practical Implications

• Example 2 is dynamic and depends on the visual's filter context. It calculates the running total up to the selected/max date in the visual.

• Example 1 & 3 are static and calculate the running total up to the last date in the entire table, regardless of visual filters.

When to Use Each Approach

• Use Example 2 when you want the running total to respect the visual's filters (e.g., a slicer selecting a specific date range).

• Use Example 1/3 when you want the running total to always go up to the last date in the table, ignoring visual filters.

This behavior is foundational to DAX's "context transition" mechanics, which are critical for mastering dynamic calculations in Power BI and Analysis Services.