Looking up columns in another tables

[PegahMV]

Hi,
I have a custom table with 5 columns. My table has no relation to other tables in my data model. I want to lookup 4 of my columns in other tables and return my 5th column if all of them match. Each column is searched in a separate table.
can you please help me

[MFelix · Accepted solution]

Hi @PegahMV ,

For this you need to use the LOOKUPVALUE formula that will allow you to do this search on other tables.
https://dax.guide/lookupvalue/

[Bibiano_Geraldo · Accepted solution]

Hi @PegahMV ,
you can create a measure that checks if all conditions are met. You can use the LOOKUPVALUE  function for this purpose by this DAX:

MatchedValue = 
VAR Col1Match = LOOKUPVALUE(Table1[Column1], Table1[Column1], 'YourTable'[Column1])
VAR Col2Match = LOOKUPVALUE(Table2[Column2], Table2[Column2], 'YourTable'[Column2])
VAR Col3Match = LOOKUPVALUE(Table3[Column3], Table3[Column3], 'YourTable'[Column3])
VAR Col4Match = LOOKUPVALUE(Table4[Column4], Table4[Column4], 'YourTable'[Column4])
RETURN
IF(
    AND(
        NOT(ISBLANK(Col1Match)),
        NOT(ISBLANK(Col2Match)),
        NOT(ISBLANK(Col3Match)),
        NOT(ISBLANK(Col4Match))
    ),
    'YourTable'[Column5],
    BLANK()
)

NOTE: Make sure to change the table and columns names with your owns

If there are multiple matches and you need to handle them, you might need to use additional logic to decide which match to return. This can be done using functions like FIRSTNONBLANK , LASTNONBLANK , or other aggregation functions depending on your specific requirements.

You can learn more about LOOKUPVALUE funtion here 

If this help you, please consider to accept as solution and  give a Kudo.

Thank you

[ryan_mayu]

pls provide some sample data and expected output

[MFelix · Accepted solution]

Hi @PegahMV ,

For this you need to use the LOOKUPVALUE formula that will allow you to do this search on other tables.
https://dax.guide/lookupvalue/