Switch function result error

[Anonymous]

Hello,

Please see below and provide your suggestions,I have used below formula but I am not getting correct result.

Comments = SWITCH(TRUE(),
         'Table2'[Seller]=RELATED(Table1[Joint Venture]),"Joint Venture",
         'Table2'[Country]=RELATED(Table1[Country]),"Dealer",
         'Table2'[Seller]=RELATED(Table1[Sub Dealer]),"sub dealer",
         "deal")

Table 2 [Seller] is master table and look for a reference in Table 1 all the columns,My formula does not give me correct result when it comes to [Joint Venture] and [Sub Dealer].

Let me know if you need more information.

Table2
Country	Seller	Comments
Algeria	ABC
Argentina	DEF
Belarus	XYZ
Brazil	KBC
Bulgaria	CBC
China	AAJ

Table1
Country	Joint Venture	Sub Dealer
Algeria	XYZ	KBC
Argentina

Correct Final Result

Table2
Country	Seller	Comments
Algeria	ABC	Dealer
Argentina	DEF	Dealer
Belarus	XYZ	Joint Venture
Brazil	KBC	Sub Deler
Bulgaria	CBC	Deal
China	AAJ	Deal

Wrong Result
Country	Seller	Comments
Algeria	ABC	Dealer
Argentina	DEF	Dealer
Belarus	XYZ	Deal
Brazil	KBC	Deal
Bulgaria	CBC	Deal
China	AAJ	Deal

[v-lili6-msft · Accepted solution]

HI, @Anonymous

You can't use RELATED Function in your case,

If you create a relationship between table1 and table2

Only first and second row of table2 could have the related data in table1

So it will due to the problem.

Try this formula

Comments = SWITCH(TRUE(),
         'Table2'[Seller]=LOOKUPVALUE(Table1[Joint Venture],Table1[Joint Venture],Table2[Seller]),"Joint Venture",
         'Table2'[Country]=LOOKUPVALUE(Table1[Country],Table1[Country],Table2[Country]),"Dealer",
         'Table2'[Seller]=LOOKUPVALUE(Table1[Sub Dealer],Table1[Sub Dealer],Table2[Seller]),"sub dealer",
         "deal")

Result:

Best Regards,

Lin

[Anonymous]

Hi Lin,

Thank you so much for unique solutions and for relationship explaniations.

[Anonymous]

Hi @Anonymous,

I would try to use an IF statement instead. I found the SWITCH to be unpredicted in the past as well.

Good luck!

[Anonymous]

Hi AClerk,

thank you so much for your suggestion. I have used the if but still no luck. i am not getting correct result and joint venture and sub dealer condition satisfied from Table 2 seller column.Thanks

[Anonymous]

OK,

You evaluate row after row with IF.

You need somekind of a vlookup functionality.

Look for it, I will try to do it myself later, when I'll get some time.

Hope it helps....

[Anonymous]

For easy understadning (Dataset and final result)

Table2		Table1 (Lookup Table)
Country	Seller		Country	Joint Venture	Sub Dealer
Algeria	ABC		Algeria	XYZ	KBC
Argentina	DEF		Argentina
Belarus	XYZ
Brazil	KBC
Bulgaria	CBC
China	AAJ

Final Result
Country	Seller	Result
Algeria	ABC	Dealer
Argentina	DEF	Dealer
Belarus	XYZ	Joint Venture
Brazil	KBC	Sub Dealer
Bulgaria	CBC	Deal
China	AAJ	Deal

[Anonymous]

Hi,

sure thank you so much. I will wait for your response.I can understand why Joint venture and sub delaer options not picked up by script.

Thanks