how to filter using another column

Hi mreig ,

I did it in three ways, please check.

• Method1: In Power Query:

Merge as new Queries --> Expand and remain only division_id column--> Add a custom column with a flag (1 or 0) --> Filter out Custom column =0:

The final output is shown below:

• Method2: Use DAX to add a new table:

As Anonymous said, you could use LOOKUPVALUE() to create a new table like this:

New Table = 
var _t=ADDCOLUMNS('Sale_order',"division_id res", LOOKUPVALUE('Res_partner'[division_id],[Name],[Name],[ID],[ID]))
return FILTER(_t,[divison_id]<>[division_id res])

• Method3: Use DAX to create measures:

Measure = IF(MAX('Res_partner'[division_id])<>MAX('Sale_order'[divison_id]),1)

 Then apply the measure to filter pane, set as "is 1", the final output is shown below:

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