Earlier(), AddColumns() and row context

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:

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

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

, “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.

19 thoughts on “Earlier(), AddColumns() and row context

  1. have a question irrelevant to the Post .

    i have a calculated member like the folowing

    CREATE MEMBER CURRENTCUBE.[Measures].[Active Handset Count]
    AS [Measures].[Active Handset Count – All],
    VISIBLE = 1;

    and a scope for that member

    SCOPE([Network].[Sector Technology].[Sector Technology],[Measures].[Active Handset Count]);
    THIS = [Measures].[Active Handset Count – Sector];
    Format_String ( This ) = “#,0”;

    when i run the following query

    {[Measures].[Active Handset Count – Sector],[Measures].[Active Handset Count]} on 0
    from [daxm]
    ({[Network].[Sector Technology].&[GSM],[Network].[Sector Technology].&[Unknown]})

    it returns null for active handset count

    but the following by removing one of the members in the slice it works it returns a good value

    {[Measures].[Active Handset Count – Sector],[Measures].[Active Handset Count]} on 0
    from [daxm]
    ({[Network].[Sector Technology].&[GSM]})

    i fixed the problem by doing
    with member [avg active count] as sum({{[Network].[Sector Technology].&[GSM],[Network].[Sector Technology].&[Unknown]}},[Measures].[Active Handset Count])

    — but i would like to know why its returning null when it should return a value , i replaced the calculated member to return 1 if its not using the scope and it returned “1” so why its not entering the scope when there is 2 members in the slice from the same hierarchy .

      • Hi Chris
        you can easily reproduce the problem by creating a calculated member = 1 and a scope for that measure with any attribute hierarchy that returns 2 .
        if in the where condition you put 1 member of that hierarchy it returns 2 if you add another one it returns 1

      • Can you give me a worked example of this on Adventure Works please, and let me know which version and SP of SSAS you’re running? I think I know the issue you’re running into, but I can only be sure if I see a query.

      • Hi Chris am using
        SSAS 2008
        Microsoft Analysis Services Client Tools 10.0.1600.22
        concerning the adventure works db well as i mentioned if you just create a calculated member with constant value 1 and a scope for that measure with any attribute of an existing hierarchy like the following

        SCOPE ([Measures].[calculatedmember],[Network].[Sector].[Sector]);
        THIS = ‘2’;
        Format_String ( This ) = “#,0”;
        END SCOPE;

        if we do a query to that calculated member like
        select calculated member on 0
        from Cube
        where {attribute.member1,sameattribute.member2)

        the result is Null

        if we remove the member2 it will return value 2

        am sorry i dont have adventureworks deployed to test it there but i just did this simple test on another cube i have and same result … hope that answers ur question …

      • This is interesting – I also found that if you add a second assignment on the all member, you still get 1 returned, but if you have a single assignment that covers the all member and the level below, for example:

        SCOPE ([Measures].[calculatedmember],[Network].[Sector].members);
        THIS = ’2′;
        Format_String ( This ) = “#,0″;
        END SCOPE;

        You get 2 returned. This seems inconsistent to me and I’ve asked the dev team to comment, but all they have said so far is that there is a specific rule in the engine to make it work this way… I’ll let you know if and when I have more details.

  2. Really don’t understand why you’re pursuing DAX – it’s ill thought through and is not being used nor will it. Your reputation does not depend on getting on the MS bandwagon. Stick to Analysis Services – a good engine that copes with most requirements.

    • I’ve missed your spam comments, Jon (I assume it’s you – the IP address can be traced to central Slough)! Whatever you or I think of DAX, it’s here to stay and my reputation does depend on getting on the MS bandwagon…

  3. Hi Chris,

    Is it possible to simulate the following MDX calculation with DAX
    WITH MEMBER [Measures].[LTD] AS
    SUM({null:[someDimension].CurrentMember}, [Measures].[Amount])

    Richard Mintz

    • It’s only really useful for DAX queries, not calculations. You can run queries against PowerPivot 2012 (in which case the same syntax should work), but usually with PowerPivot you only define calculations.

  4. Maybe I’m going about this wrong as a PowerPivot newbie.. What I’m trying to do is use Earlier in either a calculated column (or measure) to simply figure out delta % differences between current row and prior row of a given column.

    For example, lets say my rows are yearly Revenue numbers in dollars. I want to then create a Revenue YoY % generated column (year over year % diff) which should be this formula:

    =[Revenue]/Earlier([Revenue] – 1

    Of course I get the dreaded “EARLIER/EARLIEST refers to an earlier row context which doesn’t exist” error when attempting this. I then tried to have it just ignore the first row like so but this doesn’t seem to work either:

    =IFERROR([Revenue]/Earlier([Revenue] – 1, Blank())

    Any ideas how to do this?

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s