Earlier(), AddColumns() and row context

Reposted from Chris Webb's blog with the author's permission.

I think just about anyone who’s tried to use the Earlier() function in DAX has come across the following error message:

EARLIER/EARLIEST refers to an earlier row context which doesn’t exist

As the documentation helpfully points out:

EARLIER succeeds if there is a row context prior to the beginning of the table scan. Otherwise it returns an error.

Now I’m not going to try to explain what “row context” actually means right here in any detail (this article is a great place to start, and Marco and Alberto do a great job in chapter 6 of their PowerPivot book), although it is a fundamental concept in DAX that you do need to understand thoroughly, but the presence or otherwise of a row context is particularly important when using Earlier().

Take the following model with a single table sourced from the Adventure Works DimDate table. If you create a calculated column using the following expression:

=calculate(countrows(DimDate), All(DimDate), DimDate[DayNumberOfWeek]=earlier(DimDate[DayNumberOfWeek]))

It works fine, as shown below:

For each row, we’re saying that we want to do another scan of the table and find out how many rows in the entire table have the same value in the DayNumberOfWeek column as the current row. Earlier() needs at least two table scans to work and we have that here: one which is inherent in the nature of creating a calculated column, and one that is triggered by the Calculate statement.

However, if you try to use exactly the same expression in a measure, you get the error above:

This is because when a measure is evaluated any external row context is automatically turned into a filter context, so in this case there’s only one row context – that created by the Calculate statement itself.

Anyway, this is all well-documented stuff and something we should all know (though, I guess like everyone else, I’m still relatively new to DAX and forget these things from time to time…). Recently I was playing around with DAX queries and found something I really couldn’t understand. Using the example model above, I found that while creating a measure in a query failed as I would expect:

define
measure dimDate[Demo] =
calculate(countrows(DimDate)
, All(DimDate)
, DimDate[DayNumberOfWeek]=earlier(DimDate[DayNumberOfWeek]))
evaluate dimDate

I found the following query, where the same DAX expression was used in AddColumns(), worked:

evaluate
addcolumns(
dimDate
, “Demo”
, calculate(countrows(DimDate)
, All(DimDate)
, DimDate[DayNumberOfWeek]=earlier(DimDate[DayNumberOfWeek])))

What I came to realise after much patient explanation on the part of Jeffrey Wang and Marius Dumitru was the fairly simple fact that AddColumns() is not the same as creating a measure – in fact it’s like the first example above in that it behaves as if you are creating a new calculated column on the table returned by the query, and indeed we get the same values returned as we get in the calculated column example above. Therefore, with AddColumns(), we have the two row contexts we require to make Earlier() work.