How to remove rows where a column contains a value that's not present in another column in any rows?

[pythongtdax]

I have two columns in a table with two columns Person and Manager as below.

Person	Manager
0001	0003
0002	0003
0003
0004	0005

I want to remove the last row, i.e the one with Person = 0004 and Manager = 0005 as 0005 is not present in the person column for any of the rows. 

The end result I want would thus be: 

Person	Manager
0001	0003
0002	0003
0003

How do I do this in Power BI? 

[v-stephen-msft]

Hi @pythongtdax ,

Here's the sample data I created.

First create a calculated column using LOOKUPVALUE function.

Column = IF(ISBLANK(LOOKUPVALUE('Table'[Person],'Table'[Person],'Table'[Manager]))&&NOT(ISBLANK([Manager])),"N","Y")

Then you can add the column into the slicer for filtering.

Or add to visual-level filters.

Best Regards,

Stephen Tao

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

[kumarthombre20 · Accepted solution]

@pythongtdax Can you try this 

Unique List Flag = 
CONTAINS(tblPerson,tblPerson[Person],tblPerson[Manager]) || CONTAINS(tblPerson,tblPerson[Manager],tblPerson[Person])

[Samarth_18]

Hi @pythongtdax ,

If I took your question correctly then I think you can use filter on visual like below or you can remove it from PQ.

Thanks,

Samarth