Multiple columns error?

Hi rmcgrath ,

The error you’re encountering comes down to how DAX interprets scalar and table values within FILTER. Let’s break down the issues and the differences between the two formulas if they were valid.

In DAX, the FILTER function expects its second argument (the condition) to evaluate to a scalar (single value) for each row in the table context. Here’s how this causes issues in your formulas:

• FILTER(ALL('Dates Table'), 'Dates Table'[Date] <= [Max Date]): [Max Date] is a measure, which returns a single value. However, the left side of the condition ('Dates Table'[Date]) is a column reference, which implies multiple values. This results in a conflict because DAX cannot compare multiple dates in the column to the single scalar result of [Max Date].
• FILTER(ALL('Dates Table'), 'Dates Table'[Date] <= MAX(Dates Table[Date])): Here, MAX(Dates Table[Date]) is trying to calculate the maximum date in each row context, but FILTER is iterating over all rows in 'Dates Table', which means MAX(Dates Table[Date]) is evaluated over multiple rows, causing the same "multiple columns" error.

In short, DAX doesn’t know how to handle this mixed comparison of columns and measures within FILTER.

To filter dates up to the maximum date across all rows:

Test1 = CALCULATE(
    MAX('Dates Table'[Date]),
    FILTER(
        ALL('Dates Table'),
        'Dates Table'[Date] <= [Max Date]
    )
)

To dynamically apply a date filter using the row context, you could replace MAX(Dates Table[Date]) with a variable that represents the maximum date dynamically within the desired context:

Test2 = 
VAR MaxRowDate = MAX('Dates Table'[Date])
RETURN
    CALCULATE(
        MAX('Dates Table'[Date]),
        FILTER(
            ALL('Dates Table'),
            'Dates Table'[Date] <= MaxRowDate
        )
    )

These alternatives should avoid the scalar error and give the desired filtered results.

Best regards,