Forum Discussion
Many to Many model. Cardinality help.
I have two tables. Table one has an 'order number' the 'order amount' and the associated 'invoice'.
Another table has the 'order number' 'invoice' and 'invoice amount'.
The order number might have a blank invoice if there hasn't been an invoice processed yet, but all invoices will be tied to an order number.
I am trying to build a report that shows each order, the order amount, the invoices tied to that order and the invoice amount.
Example:
TABLE 1:
| ORDER | ORDER_AMOUNT | INVOICE |
| A-1234 | $800 | Z1 |
| A-1234 | $800 | Z2 |
| A-1234 | $800 | |
| A-9999 | $700 | D0 |
| A-9999 | $700 | D1 |
TABLE 2:
| ORDER | INVOICE | INVOICE_AMOUNT |
| A-1234 | Z1 | $500 |
| A-1234 | Z2 | $200 |
| A-9999 | D0 | $370 |
| A-9999 | D1 | $330 |
In this example, Order A-1234 is an $800 order, the invoices processed are Z1 and Z2 for $700 ($100 remaining to be processed on order).
I have read about the issues with a many to many relationship or a bi-directional relationship if I make a unique/distint 'order' table. These are tables available to me for my work so i have to work with it. What is the best approach.
Also I understand I can make a relationship between the Order for each table, but the 'blank' orders in Table one would give me errors I don't know how to correct (or a scenario I don't understand how to navigate).
Thanks!
3 Replies
Hi,
I am not sure how your datamodel looks like, or how your desired outcome looks like, but I tried to create a simple sample like below. All measures are in the attached pbix file.
Please check the below picture and the attached pbix file whether it suits your requirement.
I hope the below can provide some ideas on how to create a solution for your datamodel.
you just removed the invoice from the second table? Sorry I should have added, the order ID isn't unique. It's only unique to the invoice.
