Lookup values from multiple tables

[Anonymous]

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.

[mohammadyousaf]

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. 

[Greg_Deckler]

@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

[Anonymous · Accepted solution]

@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:

Table1

Count	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 3

Order	Count	user	name	Gender
1	2	432	AA	M
2	4	433	AB	F
3	5	434	AC	M

Table 4

Order	Code	Date	status
1	1239	9/13/2020	Completed
2	1240	9/15/2020	Completed
4	1241	9/16/2020	Completed

Thanks.

[Greg_Deckler]

@Anonymous You could use LOOKUPVALUE. How are your Table1 and Table3 related? What columns? And is it 

Table1  1->*  Table3 

?

[Anonymous]

@Greg_Deckler  Table 1 is related to Table 3 as One to Many by Count columns. Thanks.

[Greg_Deckler]

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