Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

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...
  • Anonymous's avatar
    Anonymous
    5 years ago

    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.
mohammadyousaf's avatar
mohammadyousaf
Resolver II
3 years ago

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.