Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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 |
Solved! Go to 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
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
Hi Lin,
Thank you so much for unique solutions and for relationship explaniations.
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!
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
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....
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 |
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
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
User | Count |
---|---|
87 | |
78 | |
62 | |
61 | |
60 |
User | Count |
---|---|
160 | |
114 | |
100 | |
74 | |
65 |