vlookup or merge 2 tables bases on 2 columns -OR

[Anonymous]

I have 2 tables :

2020

SOLDTO	SOLDTO_NAME
199	C A GROUP
379	MG Group
680	RK Group

2018

SOLDTO	SOLDTO_NAME	Active
199	C A GROUP	No
370	MG Group	Yes
123	Dunder Group	Yes

I want to add the Active column from the 2018 table to the 2020 table based on SOLDTO  or SOLDTO_NAME 

for example, if SOLDTO  doesn't match it should check for SOLDTO_NAME

2020 COmbined

SOLDTO	SOLDTO_NAME	Active
199	C A GROUP	No
379	MG Group	Yes
680	RK Group

 It should get the Active column values for "MG Group" using the SOLDTO_NAME

[v-zhenbw-msft · Accepted solution]

Hello @AakashLoya ,

We can create a calculation column to meet your needs.

Column = 
var _x = CALCULATE(MAX('2018'[Active]),FILTER('2018','2018'[SOLDTO]='2020'[SOLDTO]))
var _y = CALCULATE(MAX('2018'[Active]),FILTER('2018','2018'[SOLDTO_NAME]='2020'[SOLDTO_NAME]))
return
IF(
    ISBLANK(_x),_y,_x)

If you don't meet your requirements, could you show the exact expected result based on the table you shared?

Best regards

Community support team _ zhenbw

If this post helps,then consider Accepting it as the solution to help other members find it faster.

BTW, pbix as an attachment.

[amitchandak]

@Anonymous , the merge is a power query can help left join on first table

https://radacad.com/append-vs-merge-in-power-bi-and-power-query

refer my video if needed -https://www.youtube.com/watch?v=zNrmbagO0Oo

[Anonymous]

Hi Amit, Thanks for your reply, but my question is different, I want to merge the tables bases on the 2 values, that if 1st doesn't match then that case check for the second value