Identify the duplicates in complex data set

[Anonymous]

Hi,

I am sorry, but it is me again. Your formula was great, but it brought up another few issue,s after i started using big data set.

As i mentioned i included index but it didn't seem to resolve the issue for THE bigger data set.

So i will use another example to explain:

 I need to identify how many duplicated activities there are raised for the same post code and street within the last 24 hours since completion date.  However, first activity after previous activity was completed wouldn't count as duplicate as it is classed as a new entry, but any other activity after would be duplicate.

The formula which i use don't seem to like that completion date is the same hours and seconds. But also, includes some of activities, whihc are completed at the same time but the first activity was raised later then second one.

Worth to mention that i sorted the data in advance editor :  #"Sorting" = Table.Sort(#"Changed Type",{{"Postcode", Order.Ascending}, {"CompletedDate", Order.Ascending},{"Street", Order.Ascending}})

Also,I included Index based on post code and used the below formula in Report tab and not Data  . Example data attached.

Activity	RaisedDate	CompletedDate	Street	Postcode	OpsRegion_Join
0123 456ABCD	01/01/1980 15:19	02/01/1980 12:00	Little street	AB1 2CD	Eastern South
0123 456BCDE	01/01/1980 15:26	02/01/1980 12:00	Little street	AB1 2CD	Eastern South
0123 456CABFE	01/01/1980 15:52	02/01/1980 12:00	Little street	AB1 2CD	Eastern South

Duplicated(24hrs) =

var dupCount = CALCULATE(COUNTROWS(Table1),FILTER(Table1,Table1[Postcode] = EARLIER(Table1[Postcode])&& Table1[Street]=EARLIER(Table1[Street])&& Table1[CompletedDate]>=EARLIEr(Table1[CompletedDate])-1&&Table1[CompletedDate]=Table1[CompletedDate] && Table1[CompletedDate]<=EARLIER(Table1[CompletedDate])&& Table1[Index PostCode]=Table1[Index PostCode] &&Table1[Index PostCode]<=EARLIER(Table1[Index PostCode])))

var lastraiseddate = CALCULATE(MIN(Table1[RaisedDate]),FILTER(Table1,Table1[Postcode]=EARLIER(Table1[Postcode])&& Table1[Street]=EARLIER(Table1[Street])&& Table1[Index PostCode]=EARLIER(Table1[Index PostCode])-1))

RETURN IF(Table1[RaisedDate]<=Table1[CompletedDate]&& Table1[RaisedDate]>=lastraiseddate&& dupCount>1,"Y","N")

Thank you so much for your help in advance.

[Anonymous]

Hi Lydia,

I included Index based on post code and used the below formula in Report tab.

Index PostCode =

RANKX ( Table1, Table1[Postcode],, ASC, DENSE )

I am not allowed to share an actual data, but i will make some exaples and will post it asap.

Thank you

Kind rEgards,

Dovile

[v-yuezhe-msft]

@Anonymous ,

How do you add index in your table? Could you please share complete data of this table and post expected result based on the sample data here?

Regards,
Lydia