Find the duration of the event

[Anonymous]

ID	Event	time
1	start	06-04-2020 15:00
1	mid time	06-04-2020 15:10
1	end	06-04-2020 15:14
2	start	06-04-2020 18:00
2	mid time	06-04-2020 18:20
2	end	06-04-2020 18:40

I would like to find duration of each event - so to have in table that event "1" latest 14 min and event 2 lasted 40 min 

[amitchandak · Accepted solution]

@Anonymous , Try a measure like

sumx(summarize(table, table[ID], "_1", datediff(minx(filter(table,table[Event]="start"),table[time]),maxx(filter(table,table[Event]="end"),table[time]), minute)),[_1])

[v-diye-msft]

Hi @Anonymous 

kindly find my below results:

Measure = var a = MINX(FILTER(ALL('Table'),[ID]=MAX('Table'[ID])),[time ])
var b = MAXX(FILTER(ALL('Table'),[ID]=MAX('Table'[ID])),[time ])
return
DATEDIFF(a,b,MINUTE)

 

[amitchandak · Accepted solution]

@Anonymous , Try a measure like

sumx(summarize(table, table[ID], "_1", datediff(minx(filter(table,table[Event]="start"),table[time]),maxx(filter(table,table[Event]="end"),table[time]), minute)),[_1])

[Anonymous]

@amitchandak  Would it be possible to apply the solution as a calculated column instead of measure? 

[Anonymous]

@amitchandak So this is my orginal table and I am trying to calculate timw between "All Fast" and "Pilot off". the table is called "PortStayEvents (R)" So my code looks like this:

But it doesnt work... Could you advise me what to do ? 

[harshnathani · Accepted solution]

Hi @Anonymous ,

Just helping you on the above solution. 

You are creating a Calculated Column, while the solution is given for a measure.

Create a New Measure

Regards,

Harsh Nathani

[Anonymous]

Hi @amitchandak , Thank you for your suggestion. The problem is that I have 300 different IDs.. and in your solution you calculated time for only one ID - "1" right ? 

[amitchandak]

@Anonymous , No, it is for everything

_1 is the name of the column in summarize table