How to lookup to another table? Vendor list and open invoices

[daydream1989]

I have a list (List 1) with all the open invoices from all vendors. And I have another list (List 2) of critical vendor list. How do I create a new column in the 1st list that if the vendor code in list 2 is the same as vendor code in list 1, it will show 'Yes', and if there is no vendor code in list 2 to match with list 1, it will show 'No'?

[v-zhouwen-msft]

Hi @daydream1989 ,

Table data and model views are as follows:

Please follow these steps:
1. Use the following DAX expression to create a calculated column .

Column = IF
(
    (RELATED(Tabelle2[Vendor]) = [Vendor]),
    "YES","NO"
)

2. Final output

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

[daydream1989]

Hi Wen Bin,

I have another rule need to add in the DAX because in my table 2 with critical vendors, there are company code and vendor code. If i match only vendor code to my table 1 with open invoices, it will clash with other company code that has same vendor code. Can you help me?

[daydream1989]

can you help me with the dax？ 

[Ritaf1983]

Hi @daydream1989 
I attached a link with a tutorial in the previous response.

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

[Ritaf1983]

Hi @daydream1989 
There are a few methods to get the desired result.

1 . With Power Query :

https://smantindata.com/value-exists-in-another-column-power-query/

2. With dax

https://powerbidocs.com/2020/08/17/how-to-check-table-1-value-exist-or-not-in-table-2-without-any-re...

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