Calculated column based on rows position

[DanielV91]

Having the above table where delta is the difference between curent timestamp and previous one, I want to calculate the column for the far right. Which function should I use, or how can I approach this? Doesn't matter if it is DAX or Power M query.

Thanks!

[v-juanli-msft]

Hi @DanielV91

Try this formula

calculated column =
IF ( [delta] > 2, "Observation2", "Observation1" )

Best Regards

Maggie 

[DanielV91]

Thanks for replying but that formula will not work. I will end up having "Observation 1" for everything except the rows where delta is greater than 2.
The observations need to be incremented when delta greater than 2 is found for the same ID while going down the table. Also, when the ID changes, observations need to start again / reset.

My programing logic is like this:

1. For all IDs 
   1. declare a counter = 1 
   2. For all rows 
      • IF (delta <= 2 OR delta = NULL) { "Observation " + counter' }
      • ELSE { counter +1; "Observation " + counter; }
   3. reset counter to 1

[alexei7]

Hi Daniel,

I think the following calculated column will do the job for you.

Can you try it and let me know how you get on (obviously replacing table and column names with your own):

Column = "Observation "&CALCULATE(COUNTROWS(Table1),ALLEXCEPT(Table1,Table1[id]),Table1[timestamp]<EARLIER(Table1[timestamp]),Table1[delta]>2)+1

Hope that helps,

Alex

[DanielV91]

@alexei7 thanks for replying! It is very close, but the ones where delta is greater than 2 should have asigned the next number. 
Basically for the ones that I marked with red should be '2'.

[alexei7 · Accepted solution]

Ah yeah, sorry.

Try it with an equals sign as well as the "<":

Column = "Observation "&CALCULATE(COUNTROWS(Table1),ALLEXCEPT(Table1,Table1[id]),Table1[timestamp]<=EARLIER(Table1[timestamp]),Table1[delta]>2)+1

Alex

[DanielV91]

@alexei7 Thank you! Works now.