Forum Discussion
Calculated column based on rows position
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!
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
6 Replies
Hi DanielV91
Try this formula
calculated column = IF ( [delta] > 2, "Observation2", "Observation1" )
Best Regards
Maggie
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:
- For all IDs
- declare a counter = 1
- For all rows
- IF (delta <= 2 OR delta = NULL) { "Observation " + counter' }
- ELSE { counter +1; "Observation " + counter; }
- reset counter to 1
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
- For all IDs
