Identify duplicates

[kellyylx]

Hi, I have this unsorted table 

contact name	last activity > 2 years ago
Adam	TRUE
Olivia	FALSE
Pauline	FALSE
Clarice	TRUE
Dominic	FALSE
Ruby	FALSE
Olivia	TRUE
Ruby	FALSE
Adam	FALSE
Adam	FALSE

and i want to filter out these records where at least one of the duplicate records have last activity > 2 years ago as TRUE

contact name	last activity > 2 years ago
Adam	TRUE
Adam	FALSE
Adam	FALSE
Olivia	TRUE
Olivia	FALSE

it would be great if i can do this under the table view instead of in transform data because the table is very large and operations are very slow. but if there is no way to do this under the table view, please let me know how to do it in the Advanced editor as well.

Thanks in advance!

[rajendraongole1 · Accepted solution]

Hi @kellyylx - Create a New Calculated Column: Add a DAX calculated column to your table to check whether any record for each contact name has last activity > 2 years ago set to TRUE.

replace with your model table name use below logic:

calculated column:

FlagHasTrue =
CALCULATE(
MAX('YourTableName'[last activity > 2 years ago]),
ALLEXCEPT('YourTableName', 'YourTableName'[contact name])
)

Now, create a table or matrix visual where you can apply a filter on this new FlagHasTrue column. Set the filter condition to show rows where FlagHasTrue = TRUE. This will retain all rows of the contact names that have at least one record with "last activity > 2 years ago" as TRUE.

Hope this helps.