Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

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...
Need Help
  • v-jiascu-msft's avatar
    v-jiascu-msft
    7 years ago

    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"
    )

    Identify-duplicate-entries

     

    Best Regards,
    Dale

  • Anonymous's avatar
    Anonymous
    7 years ago

    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's avatar
    v-jiascu-msft
    7 years ago

    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

v-jiascu-msft's avatar
v-jiascu-msft
Icon for Microsoft Employee rankMicrosoft Employee
7 years ago

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's avatar
Anonymous
Not applicable
7 years ago

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's avatar
    v-jiascu-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    7 years ago

    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"
    )

    Identify-duplicate-entries

     

    Best Regards,
    Dale

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      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's avatar
        v-jiascu-msft
        Icon for Microsoft Employee rankMicrosoft Employee
        7 years ago

        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