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]),"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

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

     

     

     

     

     

     

     

7 Replies

  • v-lili6-msft's avatar
    v-lili6-msft
    Community Support
    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 Lin,

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

  • 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....