Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Switch function result error

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]...
Need Help
  • v-lili6-msft's avatar
    v-lili6-msft
    7 years ago

    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's avatar
Anonymous
Not applicable
7 years ago

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's avatar
Anonymous
Not applicable
7 years ago

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's avatar
    Anonymous
    Not applicable
    7 years ago

    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's avatar
      Anonymous
      Not applicable
      7 years ago

      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

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      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