Skip to main content
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Helper II
Helper II

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

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.









Helper II
Helper II

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.  




Helper II
Helper II

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.

JaclynPugh2022_0-1665402837369.png and I have tried this way:



Future Quantity =
    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).


Helper II
Helper II

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.  

Super User
Super User

@JaclynPugh2022 , Hope you created table like

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


Always use products from All Product

Helpful resources

Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City


Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.