Problem with if formula between two different tables

[Pastrupgaard]

Hi,

I'm trying to make a connection between two different tables and I'm having trouble figuring out how to do it the best way. I have the following two tables:

     - d_product

     - f_sales

In both tables i have a unique field called [Item_no_key]. What i'm trying to do is: If d_product[item_no_key] = f_sales[item_no_key] then write f_sales[actual ($)] in a new column in d_product. 

I would appreciate any and all advice 🙂

Br
Pastrupgaard

[jthomson]

I'm not entirely sure why you'd need to pull it from one table to another given the relationship you have, but the RELATED function should be able to do this?

[Pastrupgaard]

Hi @jthomson

I just saw that the d_product[item_no_key] is unique in this table, and f_sales[item_no_key] is not, doesn't that make any difference? Can you give an an example of how the RELATED function works? 

Br
Pastrupgaard

[v-huizhn-msft]

Hi @Pastrupgaard,

Create a relationship between d_product[item_no_key] and f_sales[item_no_key](one-to-many) relationship. "What i'm trying to do is: If d_product[item_no_key] = f_sales[item_no_key] then write f_sales[actual ($)] in a new column in d_product." For one row in d_product table, there are mutiple f_sales[actual ($)] in f_sales table, how calculate? Please use a aggregate function, like SUM, MAX, MIN and so on.

Best Regards,
Angelia