Dealing with many to many relationship

[Anonymous]

HI ,

I need help on one to many relationship.

I three tables : 

1. Table 1 - FRS Hold Rls Date
2. Table 2 - FRS Hold Measures
3. Table 3- FRS Inventory Measures, and following is how they are joined.

This is a mock re-creation of the type of data fields and data in the three tables

I need to create a table listing RLS Date and (1) Count of Steel ID (2) Total weight against Steel ID. Although Count of ID is giving the correct result , the weight is not showing correct total. It will be great help if the forum can advise why am I getting the total of all weight for each RLS Date and not the total against each date. How can we resolve this. 

This is how it looks like currently.

Thanks.

[mahoneypat · Accepted solution]

Your Date and Hold Measures are not filtering your Inventory measures table.  You can pass the SteelID values over to that table with TREATAS.  See if this gets your expected result.

New Sum = CALCULATE(SUM(Table3[Weight]), TREATAS(VALUES(Table2[SteelID]), Table3[SteelID]))

Pat

[mahoneypat · Accepted solution]

Your Date and Hold Measures are not filtering your Inventory measures table.  You can pass the SteelID values over to that table with TREATAS.  See if this gets your expected result.

New Sum = CALCULATE(SUM(Table3[Weight]), TREATAS(VALUES(Table2[SteelID]), Table3[SteelID]))

Pat

[Anonymous · Accepted solution]

Thanks a lot , It worked.