Forum Discussion
LOOKUPVALUE Question
Hello,
The below code looks for an ID from Table1, in Table2. If it finds a matching ID, it returns a value for the ID from Table2 that I'm using in a line graph.
What I want to do is have the code look both ways; so look for an ID from Table2, in Table1, and if it finds a matching ID, return the value for that ID as well. I believe the code, as it stands, is a left outer join, where I need a full outer join.
Any ideas on how to modify the code to achieve a full outer join?
Thanks in advance to anyone who can help!
IF(ISBLANK(LOOKUPVALUE('Table1 '[Table1 - Value],'Table1'[_ID],'Table2'[_ID])),0,LOOKUPVALUE('Table1'[Table1 - Value],'Table1'[_ID],'Table2'[_ID]))
Hi ， Anonymous
I don’t know if I understand you correctly. I made a demo, I hope it will be helpful to you.
I create a calculated table by using two tables:
Table1+2 = DISTINCT(UNION(SELECTCOLUMNS(Table1,"ID",Table1[_ID]),SELECTCOLUMNS(Table2,"ID",Table2[_ID])))Then add two calculated column into the table:
Table 2 - value = IF(ISBLANK(RELATED(Table2[Table2 Value])),0,RELATED(Table2[Table2 Value]))Table 2 - value = IF(ISBLANK(RELATED(Table2[Table2 Value])),0,RELATED(Table2[Table2 Value]))it shows as below:
Best Regards,
Community Support Team _ Eason
3 Replies
Hi ， Anonymous
I don’t know if I understand you correctly. I made a demo, I hope it will be helpful to you.
I create a calculated table by using two tables:
Table1+2 = DISTINCT(UNION(SELECTCOLUMNS(Table1,"ID",Table1[_ID]),SELECTCOLUMNS(Table2,"ID",Table2[_ID])))Then add two calculated column into the table:
Table 2 - value = IF(ISBLANK(RELATED(Table2[Table2 Value])),0,RELATED(Table2[Table2 Value]))Table 2 - value = IF(ISBLANK(RELATED(Table2[Table2 Value])),0,RELATED(Table2[Table2 Value]))it shows as below:
Best Regards,
Community Support Team _ Easonv-easonf-msft Thank you! This gets me where I want to be; bringing in the values for each project ID from table 1 in to a line and clustered column chart, and all values for each project from table 2 as a separate line, whether or not the projects from table 2 exist in table 1. There are additional fields being brought in to this visualization and some nuances that I haven't explained, but suffice it to say that this works.
Wait, what? Can you provide simple sample data and expected result. There are quite a few joining functions in Power BI including INTERSECT, EXCEPT, NATURALINNERJOIN, etc. you may not have to reinvent the wheel.
