Add column based on two related tables

[Reuben]

Hi all,

I'm trying to add a new column in a table that lookups in 2 different tables:

Table1 contains columns with Table1[ID_1] and Table1[ID_2] (among other columns), in this table I want to add a new column Table1[Product]

"Product" should be:

Lookup Table1[ID_1] in Table2 and if Table2[ID_1] exists, then give me the value in the column Table2[Product]

if does not exist-> Lookup Table1[ID_2] in Table3[ID_2] and return me the value in the column Table3[Product]

I´ve tryed the following:

Table1[Product] = iferror(related(Table2[Product]);related(Table3[Product]))

but the result retunrs an error.

Thank you very much for your support

[CheenuSing · Accepted solution]

Hi @Reuben ,

Hope you have the relationship between the Table1 --> Table2 and Table1 --> Table 3

Try the following as calculated Column in Table1

ProductName = IF(ISBLANK(LOOKUPVALUE(Table2[ProductName],Table2[ID ],Table1[ID ])),LOOKUPVALUE(Table3[ProductName],Table3[ID ],Table1[ID ]),LOOKUPVALUE(Table2[ProductName],Table2[ID ],Table1[ID ]))

What it does if the LOOKUPVALUE FROM Table2 is blank then get the LOOKUPVALUE FROM Table3 otherwise LOOKUPVALUE FROM Table2.

Alternately you can change your formula as under

Product = IF (ISBLANK(Related(Table2[ProductName])),Related(Table3[ProductName]),Related(Table2[ProductName]))

Cheers

CheenuSing

[Reuben]

Thank you very much @CheenuSing ! thats exactly what I needed!