Get rid of call to EARLIER function in calculated column.

[h4tt3n]

Hello everyone,

In my reports I use a DAX function to create a calculated column containing the difference between two temperature values in adjacent rows of the same column. In order to get it right, I need to filter on two other columns containing unit id and timestamp. This results in a very unfortunate situation, where I have to call the extremely memory intensive EARLIER function, which is ~ O(n^2) twice for each row inside a filter function that evaluates the entire table. As a result, using this function on a ~42K row table consumes nearly 16 GB ram (returns an error on an 8 GB machine) and is of course very slow. This is what it looks like:

DeltaIntakeTempCC =

Data[IntakeTemp] -

LOOKUPVALUE

(

Data[IntakeTemp],

Data[Timestamp],

CALCULATE

(

MIN( Data[Timestamp] ),

FILTER

(

Data,

Data[Timestamp] > EARLIER( Data[Timestamp] ) && Data[UnitId] = EARLIER( Data[UnitId] )

)

)

)

Data is the name of the table, while IntakeTemp, Timestamp, and UnitId are integer columns in the table.

Can you please help me formulate this same function in a way that is considerably less memory intensive? The culprit here is to get rid of the calls to the EARLIER function. Funny enough, doing this in Excel is trivial, but I am at my wit's end on how to do it in Power BI.

Cheers & thanks in advance, Mike

[Anonymous]

Hi @h4tt3n 

the EARLIER functionality can be replaced by using variables to store the current values that you want to you in your filter.

Something like: 

Measure =

VAR currentTimestamp = Data[Timestamp] 

return 

....

filter (

data,

Data[Timestamp] > currentTimestamp)

...