DAX query across 3 tables

[TAD80]

Hi, 

I am looking for hlep please. Apologies if it has been asked already. 

I have 3 tables, each with a Product Name column, i have created relationships between them all.

I need to look up the 'Product Name' (A column),  if matched in Table 2 (D column), then populate 'Owner' (B column) with the 'Cost Centre'. If it doesn't exist in Table 2 then check table 3. If it doesn't exist in Table 3 then populate 'Owner' as "N/A". 

[TAD80]

@ dallasbabatunde That works! Thanks so much.

[DallasBaba]

@TAD80 you can modify the formula to ensure that it correctly handles data from Table 3.

= IF (
NOT ( ISBLANK ( LOOKUPVALUE ( Table2[Product Name], Table2[Product Name], [Product Name] ) ) ),
LOOKUPVALUE ( Table2[Cost Centre], Table2[Product Name], [Product Name] ),
IF (
NOT ( ISBLANK ( LOOKUPVALUE ( Table3[Product Name], Table3[Product Name], [Product Name] ) ) ),
LOOKUPVALUE ( Table3[Department Name], Table3[Product Name], [Product Name] ),
"N/A"
)
)

Note: 

if the "Product Name" is not found in Table 2, it checks Table 3 for the same value.
If it exists in Table 3, it populates the "Owner" column with the corresponding "Department Name" from Table 3.
If it doesn't exist in either table, it sets "Owner" to "N/A."

Please let me know if this works

Thanks

[TAD80]

@DallasBaba Thanks very much for your response. That nearly works, it is pulling in from one table but not correctly pulling in the data from Table 3. For example, it should have pulled in a department name for ikj-hgf-456.

[DallasBaba · Accepted solution]

Hi @TAD80 can you try this formula

= IF (
NOT ( ISBLANK ( LOOKUPVALUE ( Table2[Product Name], Table2[Product Name], [Product Name] ) ) ),
LOOKUPVALUE ( Table2[Cost Centre], Table2[Product Name], [Product Name] ),
IF (
NOT ( ISBLANK ( LOOKUPVALUE ( Table3[Product Name], Table3[Product Name], [Product Name] ) ) ),
"N/A",
""
)
)

The above formula checks if the ‘Product Name’ exists in Table 2. If it does, then it populates the ‘Owner’ column with the corresponding ‘Cost Centre’.
If it doesn’t exist in Table 2, then it checks if it exists in Table 3.
If it does, then it populates the ‘Owner’ column with “N/A”.
If it doesn’t exist in either table, then it leaves the ‘Owner’ column blank.

Please let me know if this work.

Thanks