Forum Discussion
Trying to Create a One to Many Relationship from Many to Many Data
Hello!
I've been using this site for help on Power BI and now I have a challenge I'm working through. I have 2 sets of data, 1-Scrapped by Item # and 2-Planned Qty by Item #.
So to do the work around, I created an Item Master Table which i used to bridge between report 1 (Scrapped Qty) and 2 (Planned Qty) by Item #. Now I can display all the details by Item # but both reports also have other pieces of information that I would also like to link. For example, dates of key enteries...crop season...basically each report has a bit of the same info thats why its a many to many issue.
My question is how do I still pull in dates, key info, and other columns from report 1 & 2 if my Item Master (workaround) doesn't work?
Hope that's enough information to start.
Thanks,
Maykel
Hi,
Yes, you must create a Table for each Item. Then you create a relatiosnhip from the Many to the One side.
9 Replies
- Ashish_Mathur
Super User
Hi,
Please share the following:
- Link from where i can download your file; and
- Business question
- Expected result
Hi Ashish,
Here is a link to the file...
https://www.dropbox.com/s/mnhkt2msjcl8rzv/Planted%20%26%20Scrap%20Qty%20-%20Share%20Online.xlsx?dl=0
2 Tabs - 1, Planted Qty & 2, Scrapped Qty. My goal is to filter by Year, Month, etc.
Thanks,
Maykel
- Ashish_Mathur
Hi,
Create a seperate calendar table and create a relatioship from the date columns of the individual tables to the date column of the calendar table. In the calendar table, write calculated column formulas to extract Year and Month. IN whatever visual you create/formula you write, refer to the date column of the Calendar table.
Hi Maykel,
I don't think it will be possible to use one dim table to link multiple M:M relationship tables.
You need to create multiple dim table to mapping these tables, then you can use a primary key(date or id) as the main relationship to link these tables.Regards,
Xiaoxin Sheng
