Forum Discussion
Aggregate text events with time difference
Hello, I am trying to aggregate events and cannot find the right method to do so.
I have a data similar to this, its basiclaly a list of events with either error or succcess, a timestamp and a unique user id.
| Date | User | Event |
| 1-Jan | A | failure |
| 1-Jan | C | failure |
| 3-Jan | A | success |
| 3-Jan | C | failure |
| 4-Jan | B | failure |
| 4-Jan | B | failure |
| 7-Jan | C | success |
I am looking for a way to be able to agregate this to something of the following:
show the final resault, if the final event is success or falure, and show the time between the failures to the final success but only if it ended with success (if all failures, no time or specific value).
| User | final | diff |
| A | Success | 2 days |
| B | Fail | |
| C | Success | 6 days |
Is that possible?
Thanks.
Hi sagivh
try new table
Table = ADDCOLUMNS(summarize('Query';'Query'[User];"Lastevent";LASTNONBLANK('Query'[Event];1);"LastDate";LASTNONBLANK('Query'[Date];1));"diff";if([Lastevent]="success";DATEDIFF(FIRSTNONBLANK('Query'[Date];1);[LastDate];DAY);0))do not hesitate to give a kudo to useful posts and mark solutions as solution
3 Replies
Hi sagivh
try new table
Table = ADDCOLUMNS(summarize('Query';'Query'[User];"Lastevent";LASTNONBLANK('Query'[Event];1);"LastDate";LASTNONBLANK('Query'[Date];1));"diff";if([Lastevent]="success";DATEDIFF(FIRSTNONBLANK('Query'[Date];1);[LastDate];DAY);0))do not hesitate to give a kudo to useful posts and mark solutions as solution
Thank you so much az38! this worked. its pretty amazing to see this kind of smart query.
