Forum Discussion
Lookup values from multiple tables
Hi,
I have the below Structure where "Table 3" is the main table connected to other 3 sources with details. I'd like to create a formula (calculated column) that will lookup the "Code" value from each detail table by a specific lookup prioritization.
Ex. The search columns will be "Count" and "Order" from Table 3 and the result Column will be "Code" from any of the 3 detail tables.
First it will look for Count value in Table 1, if it finds the value, then it takes the value from column "Code" in Table 1, if the Count value is not found, then it goes to Table 2, if that is not found, then it looks for "Order" value in Table 4 and takes as a response the value from colum "Code" in Table 4.
The expected output will be :
Appreciate all suggestions.
Greg_Deckler Thanks for your suggestion. However, the full data model is pretty big and I'd like to avoid any bi-directional relationships. I was thinking of a formula like :
Lookup Column = SWITCH(TRUE(),LOOKUPVALUE(Table1[Code],Tabel1[Count],Tabel3[Count])>1,LOOKUPVALUE(Table1[Code],Tabel1[Count],Tabel3[Count]),LOOKUPVALUE(Table2[Code],Tabel2[Count],Tabel3[Count])>1,LOOKUPVALUE(Table2[Code],Tabel2[Count],Tabel3[Count]),LOOKUPVALUE(Table4[Code],Tabel4[Order],Tabel3[Order])>1,LOOKUPVALUE(Table4[Code],Tabel4[Order],Tabel3[Order]),BLANK())It seems that it works fine, but I'm not sure if I'm taking into account all possible implications....
For your testing:
Table1Count Code 1 1234 2 1235 3 1236 Table 2
Count Code Date 2 1237 9/11/2020 5 1238 9/12/2020 6 1239 9/13/2020
Table 3Order Count user name Gender 1 2 432 AA M 2 4 433 AB F 3 5 434 AC M
Table 4
Thanks.Order Code Date status 1 1239 9/13/2020 Completed 2 1240 9/15/2020 Completed 4 1241 9/16/2020 Completed
6 Replies
Anonymous Can you post that sample data as text? That way I can create a model and test. I realize that there isn't a ton of data to type in but makes it way easier.
Anonymous - To ammend this, maybe try changing your relationship direction to both on Table1 and Table3? So, thinking in your Table:
Table3[OrderID]
Table3[Count]
Table1[Code]
This should work without any calculations if you change that relationship direction to Both
Greg_Deckler Thanks for your suggestion. However, the full data model is pretty big and I'd like to avoid any bi-directional relationships. I was thinking of a formula like :
Lookup Column = SWITCH(TRUE(),LOOKUPVALUE(Table1[Code],Tabel1[Count],Tabel3[Count])>1,LOOKUPVALUE(Table1[Code],Tabel1[Count],Tabel3[Count]),LOOKUPVALUE(Table2[Code],Tabel2[Count],Tabel3[Count])>1,LOOKUPVALUE(Table2[Code],Tabel2[Count],Tabel3[Count]),LOOKUPVALUE(Table4[Code],Tabel4[Order],Tabel3[Order])>1,LOOKUPVALUE(Table4[Code],Tabel4[Order],Tabel3[Order]),BLANK())It seems that it works fine, but I'm not sure if I'm taking into account all possible implications....
For your testing:
Table1Count Code 1 1234 2 1235 3 1236 Table 2
Count Code Date 2 1237 9/11/2020 5 1238 9/12/2020 6 1239 9/13/2020
Table 3Order Count user name Gender 1 2 432 AA M 2 4 433 AB F 3 5 434 AC M
Table 4
Thanks.Order Code Date status 1 1239 9/13/2020 Completed 2 1240 9/15/2020 Completed 4 1241 9/16/2020 Completed Anonymous You could use LOOKUPVALUE. How are your Table1 and Table3 related? What columns? And is it
Table1 1->* Table3
?
can someone please help to change the above dax to use with text instead of strings.? I am getting the following error:
Function 'SWITCH' does not support comparing values of type True/False with values of type Text. Consider using the VALUE or FORMAT function to convert one of the values.
Thank you in advance.
