Calculate service degradation for multiple tools

[junny]

Hello,

I am facing an issue to calculate the total service degradation for an tool in case multiple tickets were logged in by multiple users.

refer table below, Ticket TT71020190507010, was created on 7th  & resolved on 10th, another ticket for the same tool TT71020190507011 was created on 8th & resolved on 9th. Here since the incident 7011 was created within the time frame when 7010 was active we should not include do duplicate entry and conly consider the time period of 7010 for dervice degradation time.

Also, for another tool Syndicate, TT71020190511013 & TT71020190511014 were created by different users at almost the same time duration for similiar probelm - the duplicate time period should be removed and only one value (larget one) must be considered as degradation time.

Ticket #	Tool	Start Time	Resolved Time
TT71020190507010	QRT	07-05-2019 09:23	10-05-2019 15:45
TT71020190508011	QRT	08-05-2019 12:22	09-05-2019 11:37
TT71020190508012	Sydicate	08-05-2019 17:12	10-05-2019 18:37
TT71020190510013	Sydicate	10-05-2019 09:23	11-05-2019 18:19
TT71020190511014	Sydicate	10-05-2019 10:02	11-05-2019 18:19
TT71020190511015	Sydicate	12-05-2019 03:07	12-05-2019 09:23
TT71020190511016	QRT	12-05-2019 20:23	14-05-2019 13:52
TT71020190514017	Sydicate	14-05-2019 15:09	14-05-2019 19:08
TT71020190514018	Sydicate	14-05-2019 17:11	14-05-2019 21:50
TT71020190514019	QRT	15-05-2019 21:23	15-05-2019 23:46

I have been struggling for days to get this sorted out but unable to click it. I basically want to calculate Serrvice degradation time for individual tools while also projecting the avg degradation time for overall tool set.

Cheers!

JB

[v-danhe-msft · Accepted solution]

Hi @junny ,

Based on my test, you could refer to below measures:

Rank a = COUNTROWS(FILTER(ALL('Table1'),ISONORAFTER('Table1'[Start Time],SELECTEDVALUE(Table1[Start Time]),DESC,'Table1'[Ticket #],SELECTEDVALUE(Table1[Ticket #]))&&'Table1'[Tool]=MAX('Table1'[Tool])))

Rank b = COUNTROWS(FILTER(ALL('Table1'),ISONORAFTER('Table1'[Resolved Time],SELECTEDVALUE(Table1[Resolved Time]),DESC,'Table1'[Ticket #],SELECTEDVALUE(Table1[Ticket #]),ASC)&&'Table1'[Tool]=MAX('Table1'[Tool])))

degradation time = IF([Rank b]<CALCULATE(MAXX('Table1','Table1'[Rank a]),FILTER('Table1','Table1'[Rank b]<='Table1'[Rank a])),"degradation time",BLANK())

Result(I have modified a row data to test for more situations):

You could also download the pbix file to have a view.

Regards,

Daniel He