Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe 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.
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
Solved! Go to 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/
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @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
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
pls provide some sample data and expected output
Proud to be a 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
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsUser | Count |
---|---|
120 | |
67 | |
66 | |
57 | |
50 |
User | Count |
---|---|
176 | |
83 | |
69 | |
65 | |
54 |