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

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
PegahMV
Frequent Visitor

Looking up columns in another tables

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

2 ACCEPTED SOLUTIONS
MFelix
Super User
Super User

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/


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

Bibiano_Geraldo
Super User
Super User

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

Is this post help you? Please consider to:
Accept as Solution!
Give a Kudo
Follow me on Linkedin: Bibiano_Geraldo_Mangue

View solution in original post

3 REPLIES 3
Bibiano_Geraldo
Super User
Super User

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

Is this post help you? Please consider to:
Accept as Solution!
Give a Kudo
Follow me on Linkedin: Bibiano_Geraldo_Mangue
ryan_mayu
Super User
Super User

pls provide some sample data and expected output





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




MFelix
Super User
Super User

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/


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

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