Forum Discussion
Row Value based on criteria and previous value
Hi all,
I have a table with timestamps and unique ids. What i am trying to achieve is:
For each row, look at previous 30 minutes and return "Yes" if there was same Id.
Is there a way to achieve something like this?
Table below shows an example (Not sure if table is 30 minutes, as i took random numbers)
| Time Stamp | ID | Result |
| 07:46:34 | 4 | No |
| 09:08:38 | 1 | No |
| 09:25:55 | 4 | No |
| 09:37:26 | 10 | No |
| 10:04:48 | 2 | No |
| 10:42:14 | 5 | No |
| 11:08:10 | 2 | No |
| 11:13:55 | 2 | Yes |
| 11:38:24 | 8 | No |
| 11:57:07 | 7 | No |
| 12:17:17 | 10 | No |
| 12:28:48 | 10 | Yes |
| 12:50:24 | 9 | No |
| 13:36:29 | 3 | No |
| 14:15:22 | 10 | No |
| 14:21:07 | 3 | No |
| 16:06:14 | 3 | No |
| 16:20:38 | 8 | No |
| 16:22:05 | 3 | Yes |
| 16:40:48 | 10 | No |
| 17:00:58 | 5 | No |
| 17:08:10 | 10 | Yes |
| 17:13:55 | 6 | Yes |
| 17:29:46 | 6 | Yes |
Thank you
Filarap
Hi Anonymous ,
To create two calculated column as below.
index = RANKX ( FILTER ( 'Table', 'Table'[ID] = EARLIER ( 'Table'[ID] ) ), 'Table'[Time Stamp], , ASC, DENSE )Resu = VAR inx = 'Table'[index] - 1 VAR a = CALCULATE ( MAX ( 'Table'[Time Stamp] ), FILTER ( 'Table', 'Table'[index] = inx && 'Table'[ID] = EARLIER ( 'Table'[ID] ) ) ) VAR b = 'Table'[Time Stamp] VAR diff = DATEDIFF ( a, b, MINUTE ) RETURN IF ( ISBLANK ( diff ) || diff > 30, "no", IF ( diff <= 30, "yes" ) )
4 Replies
v-frfei-msftCommunity Support
Hi Anonymous ,
To create two calculated column as below.
index = RANKX ( FILTER ( 'Table', 'Table'[ID] = EARLIER ( 'Table'[ID] ) ), 'Table'[Time Stamp], , ASC, DENSE )Resu = VAR inx = 'Table'[index] - 1 VAR a = CALCULATE ( MAX ( 'Table'[Time Stamp] ), FILTER ( 'Table', 'Table'[index] = inx && 'Table'[ID] = EARLIER ( 'Table'[ID] ) ) ) VAR b = 'Table'[Time Stamp] VAR diff = DATEDIFF ( a, b, MINUTE ) RETURN IF ( ISBLANK ( diff ) || diff > 30, "no", IF ( diff <= 30, "yes" ) )Give a try using these instruction:
Table.Group(yourTab, {"ID"}, {{"Conteggio", each Table.RowCount(_), type number}, {"res", each List.Transform(delta(_[Time Stamp]),each Duration.TotalMinutes(_)<30 ))"delta" is a function defined as follow:
let diff= (Lista as list, optional def) => [shLista= {def}&List.RemoveLastN(Lista,1) , LT=List.Transform(List.Zip({Lista,shLista}), each _{0}-_{1})][LT] in diff
Anonymous wrote:Give a try using these instruction:
Table.Group(yourTab, {"ID"}, {{"Conteggio", each Table.RowCount(_), type number}, {"res", each List.Transform(delta(_[Time Stamp]),each Duration.TotalMinutes(_)<30 ))
"delta" is a function defined as follow:
let
diff= (Lista as list, optional def) =>
[shLista= {def}&List.RemoveLastN(Lista,1) ,
LT=List.Transform(List.Zip({Lista,shLista}), each _{0}-_{1})][LT]
in
diffIs, of course, important that the list is sorted, which is implicitly assumed in the previuos script.
Here a differente way to get the same result:
let NotToOld= (Lista as list,Old as number, optional def) => let LstAcc=List.Skip(List.Accumulate(List.Sort(Lista),[o=def,d={def}],(s,c)=> [o=c,d=s[d]&{Duration.TotalMinutes(c-s[o])<=Old}])[d]) in LstAcc in NotToOldPS
I would like to attach a file, but I don't know if it's possible and how to do it