Delete rows if one column is a duplicate and a different row has a specific value

[Anonymous]

So I have multiple people assigned to different 'Tasks'. I want only 1 person assigned to each task. I know the names of the people whose tasks I want to keep. So basically:

Task	Agent
1	Joe
1	Sally
2	Patricia
3	Joe
3	Debra
4	Joe

In this example, I want to delete the duplicate tasks based on if Joe is the agent. If the task isn't a duplicate and Joe is assigned, I still want to keep it.

[v-jayw-msft]

Hi @Anonymous ,

Not sure if I understand your correctly but the formula provided by @VahidDM  is basically correct.

You might need to modify “= Joe” to “<> Joe".

Table 2 = 
FILTER (
    'Table',
    'Table'[Agent] <> "Joe"
        || CALCULATE (
            DISTINCTCOUNT ( 'Table'[Agent] ),
            ALLEXCEPT ( 'Table', 'Table'[Task] )
        ) = 1
)

Best Regards,

Jay

[VahidDM · Accepted solution]

Hi @Anonymous 

Try to add a new table with this code:

New Table =
FILTER (
    'Table',
    'Table'[Agent] = "Joe"
        || CALCULATE (
            DISTINCTCOUNT ( 'Table'[Agent] ),
            ALLEXCEPT ( 'Table', 'Table'[Task] )
        ) = 1
)

Output:

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.

Appreciate your Kudos✌️!!

[ryan_mayu]

@Anonymous 

you can use DAX to create a new table

Table 2 = 
VAR tbl1=ADDCOLUMNS('Table',"check",if('Table'[Task]=maxx(FILTER('Table','Table'[Task]=EARLIER('Table'[Task])&&'Table'[Agent]="Joe"),'Table'[Task]),"Yes"))
VAR tbl2=FILTER(tbl1,[check]="")
VAR tbl3=FILTER(tbl1,[check]="Yes"&&'Table'[Agent]<>"Joe")
return SELECTCOLUMNS(union(tbl2,tbl3),"Task",'Table'[Task],"Agent",'Table'[Agent])