The Trouble with EARLIER: When Vectorized Queries Meet Row Context

Microsoft has recently moved windowing functions from trial to being a part of the DAX language. Reviewing those functions, I noticed that I had missed EARLIER and EARLIEST in my list of DAX functions.

A general goal I have is to have table and filter functions implemented, as these impact the overall logic of the DAX query engine the most. However, I quickly ran into an issue.

What EARLIER actually does

EARLIER is DAX’s answer to “give me the value of this column from an outer, enclosing iteration.” The textbook example is a per-row rank:

SUMX(
    ADDCOLUMNS(
        Sales,
        "Rank", COUNTROWS(FILTER(Sales, EARLIER(Sales[Amount]) < Sales[Amount]))
    ),
    [Rank]
)

ADDCOLUMNS walks Sales one row at a time. For each of those rows, the inner FILTER walks Sales again. From inside the inner walk, EARLIER(Sales[Amount]) reaches back out to the Amount on the row ADDCOLUMNS is currently sitting on. So you can nest iterations but reference where you came from. EARLIEST is the same idea pushed to the outermost frame instead of “one level out.”

To make that concrete, say Sales looks like this:

ADDCOLUMNS visits each of those five rows in turn as its “current” row. For every one of them, the inner FILTER scans the whole Sales table again, and EARLIER(Sales[Amount]) for that scan always means “the Amount of the outer row ADDCOLUMNS on” i.e. a single fixed number for the entire inner scan, not whatever row FILTER happens to be looking at:

Each of those five Rank values is what ADDCOLUMNS attaches to its matching row, and SUMX at the top just adds the Rank column back up: 4 + 3 + 2 + 1 + 0 = 10. Put simply, it evaluates “how many rows are bigger than me,” doing two independent walks over the same table at once, with EARLIER making it clear which loop the data is from.

This is a very natural thing to want if you grew up writing calculated columns in Excel or PowerPivot, where every formula genuinely does execute one row at a time. It’s a much stranger thing to want if your engine’s whole reason for existing is to not do that.

This goes against the whole reason I use Polars

DAX-rs is built on Polars, and the entire point of Polars is that you don’t touch rows one at a time. FILTER(table, condition) normally compiles the condition into a single columnar operation — evaluate the whole thing as one Polars boolean Series over the entire table in one shot, and use that Series as a mask. There’s no loop, no “current row” variable, nothing to reach backward from. The condition is evaluated once, over everything, simultaneously.

EARLIER assumes the opposite universe: that there’s a live stack of rows currently being visited, and you can ask “what was the value two frames up?” In a vectorized evaluation, that stack simply doesn’t exist. The condition never occupies a single row long enough to have an “outer” one. You can’t retrofit “reach into the enclosing iteration” onto an operation that was never iterating in the first place — the concept requires row-by-row execution to even be meaningful.

To keep things simple, I could switch over to a per-row execution for all filters, but this would kill the general performance case of filters and most FILTER calls will probably never be combined with EARLIER or EARLIEST.

So I chose to add complexity instead.

The actual fix: check if per-row exectution is needed, then choose a path

The core of it is a function called needs_row_context, which walks the bound expression tree and answers one question: does evaluating this expression, anywhere inside it, require a genuine per-row frame? It recurses through binary operators, function arguments, VAR bindings, SUMMARIZE/SUMMARIZECOLUMNS extension expressions, and even through stored measures (so EARLIER buried three measures deep still gets caught) — and it returns true the moment it finds an EARLIER or EARLIEST call anywhere in that tree.

I can now call this function in filter_fn before deciding how to evaluate its condition. If the answer is no, which is true for essentially every FILTER call in existence, nothing changes. The condition still compiles down to one vectorized Polars boolean mask, same as before this ever existed. If the answer is yes, I fall back to a row-by-row loop: for each row of the table, build an actual per-row context frame, evaluate the condition against just that frame, and assemble the boolean mask one value at a time instead of all at once.

The row-by-row machinery for the fallback already existed in the codebase. It is used for ADDCOLUMNS and SUMMARIZECOLUMNS, they always evaluate their extension expressions one row at a time. EARLIER’s fallback path in FILTER reuses this code, so no need for additional plumbing there.

How EARLIER actually resolves a value

Once you’re in the fallback flow, the row context itself is just a Vec of frames, each one a map from (table, column) to the current scalar value for that column at that level of nesting. Every time you descend into a new row-by-row iteration, a frame gets pushed. EARLIER(column, levels) walks that stack from the innermost frame outward, levels deep.

Calling EARLIER with no outer frame to reach into — a bare FILTER with no enclosing row-by-row iteration around it — is a real error in DAX, not a blank. dax-rs treats it the same way: no frame at that depth means an explicit error, not a silent fallback to blank.

Per-row as a performance killer

This is the challenge of coding a DAX engine, row-by-row execution kills performance if you insist on using it in situations where it is not needed. Still, it is what makes DAX a really powerful tool, it can do things that are a lot more difficult to express in SQL or other languages.

If you want to protect performance and still have all the features DAX provides, there is no choice but to allow for multiple code paths. I suspect the problem will become even bigger when we move from Polars to also support SQL-based columnar databases like ClickHouse and Snowflake.

If you want to run queries like the ranking example above yourself, the DAX REST API is the fastest way to try it. Point a request at your model and get the result back as JSON, no report or client tool required.