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:   ID Status Comment Test 1 Validated blah one 1 Unvalidated   one 2 Unvalidated blah two 3 Validate...
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.
cheezy's avatar
cheezy
Helper I
1 year ago

many thanks this worked a treat