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.
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 .
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
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.
Thanks for your feedback Chris …
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…
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
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.
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?
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?
Hello, Chris!
your formula with “addcolumn” is for bism powerpivot. How it would be for excel powerpivot
Thank you
Mikhael
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.
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?
Typo on formulas for my prior post (missing right parenthesis):
=[Revenue]/Earlier([Revenue]) – 1
=IFERROR([Revenue]/Earlier([Revenue]) – 1, Blank())
JD,
You’ve misunderstood what the Earlier() function is meant to do. It doesn’t give you the value from the previous row, it gives you the value from an earlier row context. This article might help you understand what Earlier() is meant for: http://social.technet.microsoft.com/wiki/contents/articles/1099.context-in-dax.aspx
To try to solve your problem, though, you need to take a different approach. Try looking at http://www.powerpivotblog.nl/screencast-introduction-to-time-intelligent-functions-in-powerpivot for an introduction to time intelligence calculations in DAX.
Chris
Thanks for pointing me in the right direction Chris!
I have the below function as a MEASURE in POWER BI:
Rate on Date = CALCULATE(values(‘Rate Information'[Rate]),TOPN(1,CALCULATETABLE(‘Rate Information’,’Rate Information'[EMPLOYEE ID]=EARLIER(‘Charge Activity Report'[Employee Number]),’Rate Information'[Rate Effective Date]<=EARLIER('Charge Activity Report'[Charge Date])),'Rate Information'[Rate Effective Date],DESC))
Explanation of the data below
[Charge Activity Report] File
I am working with an imported excel file that lists all labor charges which is broken out by [Charge Date], [Charge Code], and [Employee Number].
[Rate Information] File
I have another imported excel file that contains all the rate information for each employee. The Rate information file is broken out by [Employee ID], [Rate effective date], and [Rate]. This means that there are muliple entries for each employees rate as it has changed over the course of their employment.
I have created a relationship between these two files by the [Employee ID].
I am currently in need of a method for how to find the corresponding [Rate Information] for each [Charge Activity] line item. My thought process was to create a dax formula that would calcuate the corresponding [Rate] for the [Rate Effective Date] that is the closest date less than (or equal to) the [Charge Date]. The function that was suggested to be is returning the below error:
EARLIER/EARLIEST refers to an earlier row context which doesn't exist
My first instinct would not be to do this in DAX, but to try to work out the effective rate for each line item while you’re loading your data (for example using Power Query). That would be much easier and much more maintainable, and probably make your queries go faster.
Chris, thank you for your response. I actually got it to work by using a “calculated column” rather than a “measure”. However, Something occurred within my data set, and I have now receiving the error:
“A table of multiple values was supplied where a single value was expected.”
Any thoughts on how to fix this (the formula has remained the same.
My first guess is that the TopN function is returning tied rows (see https://msdn.microsoft.com/en-us/library/gg492198.aspx) and therefore returning a table with more than one row in; this would then cause the Values() function to return the error you’re getting.