Aggregate text events with time difference

[sagivh]

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. 

[parry2k]

@sagivh is there always going to be one success for each user? or there can be multiple failures and successes for each user? Measure is going to be based on that.

[az38 · Accepted solution]

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

LinkedIn

[sagivh]

Thank you so much az38! this worked. its pretty amazing to see this kind of smart query.