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

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
JaclynPugh2022
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.

JaclynPugh2022_2-1664801313065.png

 

Resulting Measure:

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

JaclynPugh2022_8-1664803150807.png

 

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_6-1664802570721.png

 

 

JaclynPugh2022_5-1664802411252.png

 

 

 

 

4 REPLIES 4
JaclynPugh2022
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.  

 

JaclynPugh2022_0-1665427927232.png

 

JaclynPugh2022
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:

JaclynPugh2022_0-1665404156055.png

JaclynPugh2022_1-1665404298449.png

Future Quantity =
SUMX(VALUES('DimDate1'[Date]),CALCULATE (
    SUM ( 'FUTURE ORDERS'[FTR QTY] ),
    USERELATIONSHIP ( 'received pivot'[Receipt Date], 'DimDate1'[Date] )
))
 
JaclynPugh2022_2-1665408217933.png

 

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
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.  

amitchandak
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

 

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

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

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

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

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