Forum Discussion

Saxon202202's avatar
Saxon202202
Helper III
3 years ago

Lookup reference multiple columns

Hello, I have two tables are data and report. In data table the following columns are Result, Reference1, Reference2. In report table the following columns are Reference1 and Reference2. I am try...
Need Help
v-xiaotang's avatar
v-xiaotang
Community Support
3 years ago

Hi Saxon202202 

Thanks for reaching out to us.

could you give the expected output of picture format? thanks

 

 

Best Regards,

Community Support Team _Tang

If this post helps, please consider Accept it as the solution to help the other members find it more quickly.

Saxon202202's avatar
Saxon202202
Helper III
3 years ago

v-xiaotang ,
Thanks for your response. Herewith attached the snapshot of desired result and formula result.

 

RESULT =
VAR RESULT1 = LOOKUPVALUE(DATA[RESULT],DATA[REFERENCE1],REPORT[REFERENCE1])
VAR RESULT2 = LOOKUPVALUE(DATA[RESULT],DATA[REFERENCE1],REPORT[REFERENCE2])
VAR RESULT3 = LOOKUPVALUE(DATA[RESULT],DATA[REFERENCE2],REPORT[REFERENCE3])
RETURN
--IF(ISBLANK(RESULT1)&&NOT ISBLANK(RESULT2),RESULT2,RESULT3)
COALESCE(RESULT1,RESULT2,RESULT3)

I am applying the above mentioned DAX code but it’s giving wrong result. Can you please explain why it’s not working and where it’s went to wrong.
I am looking for both measure and calculated column solutions. Is there any alternative way I can create a different formula to get the desired result.
Katika555 advice to me create one more addtional colunm but I don't want to create a too many columns to achieve the result.

Any suggestion and help much appreciated.
https://www.dropbox.com/s/nmjhe4zy8ep7x4g/Lookupvalue%20multiple%20columns-13-11-2022.pbix?dl=0