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