Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |