Row Context and EARLIER()

With PowerPivot there is a concept called Row Context.  The Row Context consists of a single row of a table.   Certain DAX functions have an iteration function.  One of these is SUMX().  The SUMX function iterates over the rows of the table and aggregates the values found in the column you specify.

When you create nested expressions in DAX you will come across nested Row Contexts. You can think of the first row context as a loop with the second row context being an inner loop.  As per the diagram below.

Loop with nested loopThis is all fine, however, by default the row contexts are not aware of each other, hence, if you want to refer to the value in the outer loop within an expression from the inner loop then you need to use the function EARLIER().

The EARLIER function takes two parameters, the first is the column we want to refer to and the second is the number of table scans to go back, the default is set to 1.  There is also a Refer to outer loopfunction called EARLIEST which as you might have guessed refers to the outer most table scan.  These functions are mainly used in calculated columns  created from the Power Pivot window.  To illustrate the function I’ve created a data set consisting of weekly sales.

data table

We’ll create a new column to calculate the cumulative sales

=sumx(
filter(Table1,Table1[Week]<=earlier(Table1[Week],1)),
Table1[Sales]
)

The SUMX function creates the first table scan.  The FILTER function creates the second table scan.   Let me describe what is happening here.  In the first table scan we get to row 1 and the filter function is executed which filters the rows based on the week number being less than or equal to the current row.  The Sale column for the filtered rows is then aggregated.

As the SUMX function gets to the second row the process is repeated.  This time the FILTER function is returning weeks < =  2 hence the prior week is included in the row set.

This is repeated during the rest of the table scan resulting in an aggregation that covers an increasing range.

Calculated Results

I’m using this cumulative calculation to demonstrate the EARLIER function.   There are better ways to create cumulative calculations with the time intelligence features of DAX so use this example as a learning aid.

I hope you’ve found this post useful.  This is an advanced topic so don’t be concerned if you didn’t understand at the first read.   Re-create the example using your own data set and you should hopefully get the gist of what is happening.

Over and out.

Lee

About Lee Hawthorn

Business Intelligence Consultant
This entry was posted in Power Pivot and tagged , . Bookmark the permalink.

5 Responses to Row Context and EARLIER()

  1. judgedi says:

    Hi Lee,
    Great explanation of the inner/outer contexts – I particularly love the visualizations with the 2 circles!

    However, I believe the explanation is a bit misleading. It’s not the SUMX which provides the outer context. In fact, SUMX doesn’t know which table it operates on (and thus which is the current context) before the full completion of FILTER.

    What provides the outer context is PowerPivot which is looping through all rows of the table in order to evaluate the calculated column. You can check this by doing an EVALUATE of the formula in DAX Studio. Without having a calculated field, it will fail with “EARLIER/EARLIEST refer to an earlier row context which doesn’t exist.” which will show you that the SUMX by itself is not creating an outer context.

    Keep up the good work!

    • Lee Hawthorn says:

      Thanks for the feedback. Looking back I think I was trying to show the X function giving a new row context. The filter function giving the second row context. It’s always good to learn about the inner workings of DAX. I must look at DAX Studio. Have a great weekend.

  2. Krishna says:

    hi

    EXPLANATION is GOOD. do u have any book where you explained in depth about DAX functions with examples in powerpiovt. please let me know asap

  3. Great great great explanation here of a diffcult concept. Power BI forums are full of people wandering for an explanation of this. In my case, I was trying to create a watterfall chart using a calculated column that shows the difference between a value and the previous one. Thanks to you, the final formula looks simplier and easier to read:

    previous = SUMX(FILTER(Table1;Table1[id]=EARLIER(Table1[id])-1);Table1[id])

    With this formula and index colums you can calculate anything over periods of time to date, like last week, last ten days, etc. Really useful.

    Thanks again. Regards.

Leave a Reply