Forum Discussion
Identify duplicate entries
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 |
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,
DaleHi 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
11 Replies
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,
DaleGood 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,
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