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 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

 

Need Help

8 Replies

  • katika555's avatar
    katika555
    Resolver I
    3 years ago

    Saxon202202  hi, 
    Instead of using

    COALESCE(RESULT1,RESULT2,RESULT3) in Report Table Result1 use 
    If(isblank(RESULT1)&&NOT Isblank(RESULT2),RESULT2,RESULT3)
    • Saxon202202's avatar
      Saxon202202
      Helper III
      3 years ago

      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's avatar
        katika555
        Resolver I
        3 years ago

        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's avatar
      Saxon202202
      Helper III
      3 years ago

      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? 

  • 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