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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. 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...

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors