LOOKUPVALUE Question

[Anonymous]

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!

Approved Amount =
IF(ISBLANK(LOOKUPVALUE('Table1 '[Table1 - Value],'Table1'[_ID],'Table2'[_ID])),0,LOOKUPVALUE('Table1'[Table1 - Value],'Table1'[_ID],'Table2'[_ID]))

[v-easonf-msft · Accepted solution]

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:

Pbix attach 

Best Regards,
Community Support Team _ Eason

[Anonymous]

@v-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.

[Greg_Deckler]

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.