Forum Discussion
Calculated column based on rows position
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
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
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'.