Lookup reference multiple columns

[Saxon202202]

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 trying to get the result columns into report table based on the reference columns in-between two tables.

Data table reference1 are available reference1 & 2 in report table, Data table reference2 are available reference2 & 3.

This is one to many relationships in-between two tables.

I am applying the below 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.

Any suggestion and help much appreciated. 
RESULT 1 =

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

COALESCE(RESULT1,RESULT2,RESULT3)

https://www.dropbox.com/s/nmjhe4zy8ep7x4g/Lookupvalue%20multiple%20columns-13-11-2022.pbix?dl=0

[v-xiaotang]

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]

@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

[katika555]

@Saxon202202  hi, 
Instead of using

COALESCE(RESULT1,RESULT2,RESULT3) in Report Table Result1 use 

If(isblank(RESULT1)&&NOT Isblank(RESULT2),RESULT2,RESULT3)

[Saxon202202]

@katika555 ,

Thanks for your reply and suggestion but still I have incorrect result were blanks in both reference1&2 . I am tryinng to apply the if statement but still not working. 

Any suggestion and help much appreciated. 

[katika555]

@Saxon202202 messed Reuslts I cant understand what you need to get.

If you add separate columns for results like that may be would be more clear:

[Saxon202202]

Thanks for your help and sorry for the late respones. 
I agree maybe that's simple way to do it but I don't want to create a too many columns to achieve the result. Is there any alternative suggestion.

[katika555]

Hi @Saxon202202 

RETURN
If(isblank(RESULT1)&&NOT Isblank(RESULT2),RESULT2,RESULT3)

[Saxon202202]

@katika555 ,
Can you please explain what you suggested to me. I already created DAX code but it's not working but you gave me for if statement?