Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
Anonymous
Not applicable

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.

Exce1.png

  The expected output will be :
Exce.png

 

Appreciate all suggestions.

1 ACCEPTED SOLUTION
Anonymous
Not applicable

@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
CountCode
11234
21235
31236

 

Table 2

CountCodeDate
212379/11/2020
512389/12/2020
612399/13/2020

Table 3
OrderCountusernameGender
12432AAM
24433ABF
35434ACM


Table 4
OrderCodeDatestatus
112399/13/2020Completed
212409/15/2020Completed
412419/16/2020Completed
Thanks.

View solution in original post

6 REPLIES 6
mohammadyousaf
Helper III
Helper III

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
Super User
Super User

@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

 


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

@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
CountCode
11234
21235
31236

 

Table 2

CountCodeDate
212379/11/2020
512389/12/2020
612399/13/2020

Table 3
OrderCountusernameGender
12432AAM
24433ABF
35434ACM


Table 4
OrderCodeDatestatus
112399/13/2020Completed
212409/15/2020Completed
412419/16/2020Completed
Thanks.

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

 

Table1  1->*  Table3 

 

?


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

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

Greg_Deckler
Super User
Super User

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


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors