Data Modeling - Table of Unique Items - Relationship One to Many (Helper Between 2 Tables)

[JaclynPugh2022]

Hi, I have two tables, that I am trying to get a working relationship between them by Part Number.  Each table contains duplicates, so I could not connect them, because of a Many-to-Many relationship.  To create a relationship between these two tables, I created a helper table of unique part numbers called All Products and created a one-to-many relationship between this helper table and the Historical data table called Received PO and a Future Orders data table.  However, this helper table was supposed to create a relationship somehow between these two tables that have duplicates, but I do not see that happening.  

In the image below of my data model, you can see a One-to-Many relationship between the unique items helper table called All Products, which connects between Recieved POs and Future Orders.  After this connection was created with the helper table of unique items I expected that my measures MTD etc on Future Orders items would populate with correct sum of items, but this was not happening, and instead a repeated same total sum for each month appeared in reports.  To try to fix this, I connected my Future Orders table to the DATE table, and then I noticed the monthly totals were all different; however, they are very incorrect.  I see my results are 4 million quantity for the month of October, instead of 830,000, which is the total I am looking to sum in my measure, as a sum of 'Future Orders'[Future Quantity].  See below, and let me know if there is something I am doing wrong in data modeling?  Thank you!

Data is populating correctly in the table Future Orders:  

The total sum of the Future Quantity for October 2022 should be 830,000.

Resulting Measure:

Should show 830,000 but instead MTD quantity for Oct 2022 results in 45,671,875.

Future Qty = TOTALMTD(SUM('FUTURE ORDERS'[FTR QTY]), 'DimDate1'[Date])

Data Model shows Helper Table:

All Products, connecting between Received POs and Future Orders tables in a Many to One relationship.

[JaclynPugh2022]

Hi @amitchandak , 

I have this set-up now, but the measures to sum the quantity of the Future Orders table are wrong, and it appears this table does not respond to my visual report filters.  

[JaclynPugh2022]

Hi, I created the recommended union bridge table of unique part numbers to connect my received historical orders and future orders tables, yet the totals are still wrong.  I am trying to obtian correct sum of quantity from the Future Orders table.  I"m not sure what to try next.  Can you help me please?  This is important that I find a solution.

 and I have tried this way:

Future Quantity =

SUMX(VALUES('DimDate1'[Date]),CALCULATE (

    SUM ( 'FUTURE ORDERS'[FTR QTY] ),

    USERELATIONSHIP ( 'received pivot'[Receipt Date], 'DimDate1'[Date] )

))

October should be 830,000 future quantity, but no matter what I do, I get an incorrect total even just summing the data from the table see Sum of Future Quantity (above screen shot).

[JaclynPugh2022]

Trying this now.  I was able to create a table called All Products with the Union formula, and connected the to tables to them as a bridge table.  Many to One relationship pointing to the All Products bridge table.  Now I just need help with how to work measures from the Future Orders table.  My quantities are incorrect still.  

[amitchandak]

@JaclynPugh2022 , Hope you created table like

All Product= distinct(union(distinct('Future Orders'[Product]),distinct('Received PO'[Product])))

Always use products from All Product

https://amitchandak.medium.com/power-bi-when-i-asked-you-to-create-common-tables-a-quick-dax-solutio...