Identify duplicate entries

[Anonymous]

Good Afternoon,

I hope someone can help. It is a bit confusing but I will try to explane to the best my ability.

I have unique activities which were raised and completed between certain times for certain post codes. I need to identify which of those activities may be duplicate. However, it has to match the below criteria:

1) Activities has to have the same post code and street name;

2) Have to be raised before the completion date and after the previous completion date;

3) first activity is not duplicated and only the activities which were raised after first activity for the same date period(it may be raised at different dates but completed the same date)

As an example i am attaching the table. The column (duplicated?) shows the correct answer. I need to create formula(s) which would allow me to see those answers. I know this is a bit confusing but i hope someone can assist. 

p.s. in other terms it would look like: if (activity raised date)=< (activity completed date) and if (activity raised date)> then previous (activity completed date) , (activity completed date) > (activity raised date), and activity Post code is equal to each activity post code and street.  

Activity	Raised Date	Completed Date	Street	Post code	Duplicated?
1	01/05/2017	05/05/2017	Albert road	ww57	N
2	02/05/2017	05/05/2017	Albert road	ww57	Y
3	25/08/2017	25/08/2018	Albert road	ww57	N
4	01/01/2018	06/01/2018	Albert road	ww57	N
5	12/03/2018	14/03/2018	Albert road	ww57	N
6	12/03/2018	14/03/2018	Albert road	ww57	Y
7	14/03/2018	14/03/2018	Albert road	ww57	Y
8	17/03/2018	20/03/2018	Sun road	ZE47	N
9	18/03/2018	20/03/2018	Sun road	ZE47	Y
10	10/10/2018	11/10/2018	Sun road	ZE47	N

[v-jiascu-msft · Accepted solution]

Hi @Anonymous,

Try this formula, please.

Column =
VAR dupCount =
    CALCULATE (
        COUNTROWS ( 'Table1' ),
        FILTER (
            'Table1',
            Table1[Post code] = EARLIER ( Table1[Post code] )
                && Table1[Street] = EARLIER ( Table1[Street] )
                && Table1[Completed Date] = EARLIER ( Table1[Completed Date] )
                && Table1[Activity] <= EARLIER ( Table1[Activity] )
        )
    )
VAR lastRaisedDate =
    CALCULATE (
        MIN ( Table1[Raised Date] ),
        FILTER (
            'Table1',
            Table1[Post code] = EARLIER ( Table1[Post code] )
                && Table1[Street] = EARLIER ( Table1[Street] )
                && 'Table1'[Activity]
                    = EARLIER ( Table1[Activity] ) - 1
        )
    )
RETURN
    IF (
        [Raised Date] <= [Completed Date]
            && [Raised Date] >= lastRaisedDate
            && dupCount > 1,
        "Y",
        "N"
    )

Best Regards,
Dale

[Anonymous · Accepted solution]

Thank you so much for this. However, when i enter the formula i get this error message. I cannot work it out why tho..

Thank you

[v-jiascu-msft]

Hi @Anonymous,

I'm afraid some parts aren't consistent here. 

For example, the Raised Date of Activity 2 isn't after the previous completion date. So the [Duplicated?] of Activity 2 should be N, right?

What if the Completed Date of Activity 7 is "15/03/2018"? What should the [Duplicated?] be?

Best Regards,
Dale

[Anonymous]

Good Morning,

activity 2 is duplicated becasue it is raised for the same post code and street. However, both activities were closed at the same date.

If activity 7 was closed on 15th then it wouldn't be a duplicate becasue activity 7 was raised on 14th also, activity 5 and 6 were closed on 14.

Basically, I am trying to identify how many duplicated activities raised there are since last completed activity, for the same post code and street. 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.

Does it make sense?

If it would help i can explain how that works in excel? But not sure if there is a better way with DAX.

Thank you

Kind REgards,

[v-jiascu-msft · Accepted solution]

Hi @Anonymous,

Try this formula, please.

Column =
VAR dupCount =
    CALCULATE (
        COUNTROWS ( 'Table1' ),
        FILTER (
            'Table1',
            Table1[Post code] = EARLIER ( Table1[Post code] )
                && Table1[Street] = EARLIER ( Table1[Street] )
                && Table1[Completed Date] = EARLIER ( Table1[Completed Date] )
                && Table1[Activity] <= EARLIER ( Table1[Activity] )
        )
    )
VAR lastRaisedDate =
    CALCULATE (
        MIN ( Table1[Raised Date] ),
        FILTER (
            'Table1',
            Table1[Post code] = EARLIER ( Table1[Post code] )
                && Table1[Street] = EARLIER ( Table1[Street] )
                && 'Table1'[Activity]
                    = EARLIER ( Table1[Activity] ) - 1
        )
    )
RETURN
    IF (
        [Raised Date] <= [Completed Date]
            && [Raised Date] >= lastRaisedDate
            && dupCount > 1,
        "Y",
        "N"
    )

Best Regards,
Dale

[Anonymous · Accepted solution]

Thank you so much for this. However, when i enter the formula i get this error message. I cannot work it out why tho..

Thank you

[v-jiascu-msft · Accepted solution]

Hi @Anonymous,

What's the Data type of [Activity]? It should be a numeric type to work with the formula. Can you change it or share a more accurate sample, please?

Best Regards,
Dale

[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.

[v-jiascu-msft]

Hi @Anonymous ,

I would suggest you create a new thread in this forum. 

Best Regards,

[Anonymous]

Thank you. I have created a new discussion.

Kind Regards

[Anonymous]

I really appreciate your help! Although the activity was a combination of numbers and letters i incuded Index under the query. So it all works now.

If not too much to ask i have another question/task based on this excersise. In the example i used only a date but in the practical excersise i also need to calculate completion time since previous completed acitvity for the same post code and address. I am attaching an example and how it would work in excel.

[Anonymous]

[v-jiascu-msft]

Hi @Anonymous,

My pleasure. Please try this formula.

Column =
VAR lastCompletedDate =
    CALCULATE (
        MIN ( Table2[Completed Date] ),
        FILTER (
            ALLEXCEPT ( Table2, Table2[Street], Table2[Post code] ),
            Table2[Index]
                = EARLIER ( [Index] ) - 1
        )
    )
RETURN
    IF (
        ISBLANK ( lastCompletedDate ),
        99999,
        [Completed Date] - lastCompletedDate
    )

Best Regards,
Dale