Forum Discussion
Add column based on two related tables
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:
Hi Anonymous ,
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 underProduct = IF (ISBLANK(Related(Table2[ProductName])),Related(Table3[ProductName]),Related(Table2[ProductName]))CheersCheenuSing
2 Replies
Hi Anonymous ,
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 underProduct = IF (ISBLANK(Related(Table2[ProductName])),Related(Table3[ProductName]),Related(Table2[ProductName]))CheersCheenuSing