Chris Webb's BI Blog

Analysis Services, MDX, PowerPivot, DAX and anything BI-related

Earlier(), AddColumns() and row context

with 19 comments

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:

image

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:

image

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])))

image

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.

Written by Chris Webb

September 11, 2011 at 8:24 pm

Posted in DAX

19 Responses

Subscribe to comments with RSS.

  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″;
    END SCOPE;

    when i run the following query

    select
    {[Measures].[Active Handset Count - Sector],[Measures].[Active Handset Count]} on 0
    from [daxm]
    where
    ({[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

    select
    {[Measures].[Active Handset Count - Sector],[Measures].[Active Handset Count]} on 0
    from [daxm]
    where
    ({[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 .

    Hady Ziade

    September 12, 2011 at 8:35 pm

    • Hmm, this is very strange, and it would be hard to say what’s going on without seeing your cube. But I suspect the problem could be similar to the one described here: http://mdxdax.blogspot.com/2011/08/interaction-between-mdx-subselect-and.html

      Chris Webb

      September 13, 2011 at 8:22 am

      • 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

        Hady Ziade

        September 13, 2011 at 1:32 pm

      • 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.

        Chris Webb

        September 16, 2011 at 11:37 am

      • 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 …

        Hady Ziade

        September 16, 2011 at 3:42 pm

      • 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.

        Chris Webb

        September 19, 2011 at 2:06 pm

      • Thanks for your feedback Chris …

        Hady Ziade

        September 20, 2011 at 11:25 pm

  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.

    John Doe

    September 15, 2011 at 1:32 pm

    • 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…

      Chris Webb

      September 15, 2011 at 1:48 pm

  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])
    Thanks,

    Richard Mintz

    Richard Mintz

    November 8, 2011 at 5:20 pm

    • Yes – just look for any example of a total to date calculation. For dates it’s very easy – see http://powerpivotfaq.com/Lists/TGPPF/DispForm.aspx?ID=92 for example.

      Chris Webb

      November 9, 2011 at 10:39 am

      • Thanks Chris,

        Pretty cool, I get an error though if I have multiple members selected, is there a way to make it work in a multiselect environment?

        Richard Mintz

        November 9, 2011 at 2:17 pm

      • DAX is pretty good at handling multiselect, much better than MDX. Have you tried using functions like LastDate() to get the last date in the current context?

        Chris Webb

        November 10, 2011 at 9:52 pm

  4. Hello, Chris!

    your formula with “addcolumn” is for bism powerpivot. How it would be for excel powerpivot

    Thank you
    Mikhael

    100tsky

    November 27, 2011 at 7:40 am

    • 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.

      Chris Webb

      November 27, 2011 at 12:27 pm

  5. 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?

    JD

    February 4, 2012 at 5:55 am


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

Follow

Get every new post delivered to your Inbox.

Join 3,131 other followers

%d bloggers like this: