I need help removing rows that contain both duplicate and not identical time values

[JanC]

Hi 

I need help removing both duplicate and not identical time values based on same code, name and type, then reporting the maximum time elapsed here is my data sample 

Code	Name	Start Date	End Date	Time Elapsed	Type
KA	AA	20/07/2021 8:24	20/07/2021 11:28	184	RR
KA	AA	20/07/2021 8:27	20/07/2021 11:26	179	RR
KA	AA	20/07/2021 8:37	20/07/2021 11:18	161	RR
KA	AA	20/07/2021 8:40	20/07/2021 11:16	156	RR
KA	AA	20/07/2021 11:30	20/07/2021 15:55	265	RR
KA	AA	20/07/2021 11:30	20/07/2021 15:55	265	RR
KA	NN	29/08/2021 10:06	29/08/2021 10:35	29	RR
KA	NN	29/08/2021 10:06	29/08/2021 10:35	29	RR
KA	NN	29/08/2021 12:22	29/08/2021 13:26	64	RR
KA	NN	29/08/2021 12:22	29/08/2021 13:26	64	RR
KA	NN	29/08/2021 12:22	29/08/2021 13:25	63	RR
KA	MM	15/07/2021 12:07	15/07/2021 13:10	63	RR
KA	MM	15/07/2021 12:07	15/07/2021 13:10	63	RR
KA	MM	15/07/2021 14:14	15/07/2021 14:51	37	RR
KA	MM	15/07/2021 14:15	15/07/2021 14:44	29	RR
KA	MM	15/07/2021 14:15	15/07/2021 14:51	36	RR
KA	MM	15/07/2021 16:01	15/07/2021 20:06	245	RR
KA	MM	15/07/2021 16:01	15/07/2021 20:05	244	RR
KA	MM	15/07/2021 16:01	15/07/2021 19:49	228	RR
KA	MM	15/07/2021 20:15	15/07/2021 20:34	19	RR
KA	MM	15/07/2021 20:15	15/07/2021 20:32	17	RR

This the outcome I am looking for

Code	Name	Start Date	End Date	Time Elapsed	Type
KA	AA	20/07/2021 8:24	20/07/2021 11:28	184	RR
KA	AA	20/07/2021 11:30	20/07/2021 15:55	265	RR
KA	NN	29/08/2021 10:06	29/08/2021 10:35	29	RR
KA	NN	29/08/2021 12:22	29/08/2021 13:26	64	RR
KA	MM	15/07/2021 12:07	15/07/2021 13:10	63	RR
KA	MM	15/07/2021 14:14	15/07/2021 14:51	37	RR
KA	MM	15/07/2021 16:01	15/07/2021 20:06	245	RR
KA	MM	15/07/2021 20:15	15/07/2021 20:34	19	RR

[freginier · Accepted solution]

Hi

You should remove real duplicate in Power Query and then working in DAX to create a calculate column like a flag to filter your data 

Flag = 
var MaxEnd = CALCULATE( MAX('Table'[End]),FILTER('Table',EARLIER('Table'[Name])='Table'[Name]))
var MinStart = CALCULATE( MIN('Table'[Start]),FILTER('Table',EARLIER('Table'[Name])='Table'[Name]))
return
IF( 
    MaxEnd ='Table'[End],"X",
    if(
        MinStart = 'Table'[Start],"X"
    )
)

You will get this result