Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.
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
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.
@v-xiaotang ,
Thanks for your response. Herewith attached the snapshot of desired result and formula result.
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
@Saxon202202 hi,
Instead of using
If(isblank(RESULT1)&&NOT Isblank(RESULT2),RESULT2,RESULT3)
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.
@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:
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.
Hi @Saxon202202
RETURN
If(isblank(RESULT1)&&NOT Isblank(RESULT2),RESULT2,RESULT3)
@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?
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
65 | |
63 | |
52 | |
37 | |
36 |
User | Count |
---|---|
82 | |
66 | |
61 | |
46 | |
45 |