Creating Many to Many Relationship

[gkhokher]

Hi,
I am new to power Bi.
I am having issues in data modeling in power Bi.
I have two tables that I need to connect.

WorkOrderPerDay .WorkOrderNo = Transactions.order_no

Both of the tables have more than one record for order_no in each table which makes it to have many to many relationship. I tried building a bridge table but that did not help.

I need assistance with connecting these two tables. Can someone please help?

I have attached the .pbix file.
Production EfficiencyFINAL.pbix (3.0 MB)

Thank you very much !!!

[BrianJ]

@gkhokher ,

Here’s how I would handle this from a data modeling perspective:

1. delete both the Work Order Per Day and Last 12 Hours tables - they are almost completely duplicative of other tables already in the model, and all the info can be pulled from other existing tables
2. in your transactions table, split Posting Date Time into two separate fields - a Post Date and a Post Time field, via Power Query split by delimiter function. Make sure the resulting columns are set to type Date and type Time respectively
3. mark your Dates table as a date table (critical for any time intelligence analysis)
4. connect the Dates table to the Transactions table in a unidirectional 1:M relationship
5. add a Time table to your model, @Melissa provides the M code for an excellent one here:

Time Table (Power Query M function)

Hi everyone, @DavidNealon thought it would be a nice addition if there was a Time Table Power Query M code function available in this category as well. Now I can’t take any credit because it was designed by Gina Meronek, you can find it and the full article she wrote here. When invoked the function creates a Time Table at a granularity of Minutes. If your requirement is different that can easily be fixed. Note that other changes might be required as well but to give you some ideas: For Hourl…

6. connect your Time table to your Transactions table in a unidirectional 1:M relationship with Posting Time

Presto! Optimized Star Schema data model , that should support any type of time intelligence analysis you want to do.

I hope this is helpful.

• Brian

[EnterpriseDNA]

Hi @gkhokher, did the response provided by @BrianJ help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark as solution the answer that solved your query. Thanks!

[gkhokher]

Hi @BrianJ ,

The work order table has OperationStartDate and OperationEndDate and quantities. Those quantities are for the whole time period (OperationStartDate and OperatoinEndDate). But I need to show last 12 hours quantities. So, I need to split those quantites for that 12 hours . This is why I had to create WorkOrderPerDay table as I split the rows based on the date range.

But now I need to get the fields from WorkOrderPerDay table and show it in table visual that has data from multiple tables. If I add the field from WorkOrderPerDay, all the records from the table visual disappears as this table does not have relationship with Work Order Table.
Please let me know if more info is needed.

Thank you…

[BrianJ]

@gkhokher ,

If you could please provide a mockup of the final result you’d like to see, that would be really helpful to me in providing you a solution on this.

Thanks!

• Brian

[gkhokher]

Hi @BrianJ ,

I deleted the WorkOrderPerDay table and did all the calculations I needed using Dax on Work Orders (Existing) table and it resolved the issue.

Thank you very much for your support!