Forum Discussion

cheezy's avatar
cheezy
Helper I
1 year ago
Solved

Remove duplicates based on another column value

hi

struggling with how to remove duplicate entries in this scenario:

 

IDStatusCommentTest
1Validatedblahone
1Unvalidated one
2Unvalidatedblahtwo
3Validated one
4Unvalidatedblahtwo
4Validatedblahtwo

 

what i would like to achieve is this:  

 

IDStatusCommentTest
1Validatedblahone
2Unvalidatedblahtwo
3Validated one
4Validatedblahtwo

 

so if a record has same ID then delete the record that has an Unvalidated status 

 

any help appreciated

 

thanks

Need Help
  • mdaatifraza5556's avatar
    mdaatifraza5556
    1 year ago

    Hi cheezy 

    You can create a calculated table using DAX the below to get your result.


    CleanedTable =
    VAR ValidatedIDs =
        SUMMARIZE(
            FILTER('Table','Table'[Status] = "Validated"),
            'Table'[ID]
        )
    RETURN
        FILTER(
            'Table',
            'Table'[Status] = "Validated" ||
            (
                'Table'[Status] = "Unvalidated" &&
                NOT 'Table'[ID] IN ValidatedIDs
            )
        )
     

     

    If this answers your questions, kindly accept it as a solution and give kudos.

2 Replies

  • mdaatifraza5556's avatar
    mdaatifraza5556
    Super User
    1 year ago

    Hi cheezy 

    You can create a calculated table using DAX the below to get your result.


    CleanedTable =
    VAR ValidatedIDs =
        SUMMARIZE(
            FILTER('Table','Table'[Status] = "Validated"),
            'Table'[ID]
        )
    RETURN
        FILTER(
            'Table',
            'Table'[Status] = "Validated" ||
            (
                'Table'[Status] = "Unvalidated" &&
                NOT 'Table'[ID] IN ValidatedIDs
            )
        )
     

     

    If this answers your questions, kindly accept it as a solution and give kudos.