Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to Solution.
Hi @filarap ,
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
diff
Is, 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
NotToOld
PS
I would like to attach a file, but I don't know if it's possible and how to do it
Hi @filarap ,
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" ) )