Retrieving data from other table with many-to-many relationship

[Anonymous]

Hi,

I'm trying using RELATED() function to retrieve the expiry date from the other table to one table but the relationship is not directly between those 2 tables and the relationship between the others also involve many-to-many relationship. I've also tried using DAX where it takes the max date but it is not what I want. I want to retrieve the spesific date according to it's unique value/identifier. For example, ITEMID=001 has different expiry date determined by its spesific batch id. Here is the relationship (picture) and basically i want to retrieve EXPDATE from INVENTBATCH into INVENTTRANS.

Is there any way using DAX that I can retrieve the value according to it's related foreign key?

[dax]

Hi @Anonymous ,

If possible could you please inform me more detailed information(such as your expected output and your sample data)? Then I will help you more correctly.

Please do mask sensitive data before uploading.

Thanks for your understanding and support.
Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi @dax 

So here is the list of the relationship

1. INVENTBATCH.ITEMID *--* INVENTTABLE.ITEMID

2. INVENTTABLE.ITEMID *--* INVENTTRANSORIGIN.ITEMID

3. INVENTTRANSORIGIN.RECID 1--* INVENTTRANS.INVENTTRANSORIGIN

I want to retrieve the expiry date for each ITEMID (duplicate ITEMID with different batch id) which is located in INVENTBATCH and place it in INVENTTRANS. Here is the result that I want in INVENTTRANS table (picture). I want to get the data as I need to create a calculated field in the table for new date. Thanks 🙂

• ITEMID taken from INVENTTRANS
• INVENTBATCHID and EXPDATE taken from INVENTBATCH