Multiple to Single "VLOOKUP" with multiple tables

[Anonymous]

Hello,

I am trying to do what in excel would be a "VLOOKUP plus if not already in list keep otherwise move onto next match" in power Bi but I'm having some trouble. Essentially here is my data

Table 1: (Grid is non unique, ID is unique)

Grid	ID
Apple	1
Mandarin	2
Peach	3
Banana	4
Mandarin	5

Table 2: (Grid is unique, Color is miscellaneous info and is non unique)

Grid	Color
Apple	Red
Mandarin	Orange
Banana	Yellow

Table 3 (One im trying to make using Dax functions, Grid should be non unique, ID should be unique, Color is non unique based directly on table 2 info)

Grid	ID	Color
Apple	1	Red
Mandarin	2	Orange
Banana	4	Yellow
Mandarin	5	Orange

This is essentially what I want but it has 15000 data points that need to be filtered and in a timely manor. I have been trying to use the following to achieve table 3:

ID = CALCULATE(FIRSTNONBLANK(Table1[ID],0),FILTER(ALL(Table1),Table1[Grid]=Table3[Grid])) 

Color = CALCULATE(FIRSTNONBLANK(Table1[Color],0),FILTER(ALL(Table2),Table2[Grid]=Table3[Grid]))

but my results are

Table 3 (my actual output)

Grid	ID	Color
Apple	1	Red
Mandarin	2	Orange
Banana	4	Yellow
Mandarin	2	Orange

As you can see the ID is not correct because it defaults to the first "Grid=Grid" when it needs to check if an ID is already in the output and if so skip to the next instance. I know the problem is FIRSTNONBLANK but what is the correct function to use in its place to solve this problem? Also, I do not want to be destructive on table 1 or 2 as they will be frequently updated. I'm fairly new to the power bi world so sorry if this is a silly question. Thank you!

[speedramps · Accepted solution]

Click here to download a solution  

Learn about relationships here 

Thanks for reaching out for help.

I have helped you, now please help me by giving kudos.

Remeber we are unpaid volunteers.

Click the thumbs up and accept as solution button. 

One question per ticket please. If you need to extend your request then please raise a new ticket.

You will get a quicker response and each volunteer solver will get the kudos they deserve. Thank you !

[vapid128 · Accepted solution]

Table3 = 
CALCULATETABLE(
    SELECTCOLUMNS(Table1,"Grid",Table1[Grid],"ID",Table1[ID],"Color",RELATED(Table2[Color])),
    Table2[Color]<>BLANK()
)

[speedramps · Accepted solution]

Click here to download a solution  

Learn about relationships here 

Thanks for reaching out for help.

I have helped you, now please help me by giving kudos.

Remeber we are unpaid volunteers.

Click the thumbs up and accept as solution button. 

One question per ticket please. If you need to extend your request then please raise a new ticket.

You will get a quicker response and each volunteer solver will get the kudos they deserve. Thank you !