ambiguous relationships in Power BI model

[ConstMoss]

hello everyone - 

we have the following transaction tables - orders (from customers), invoices, and ledgers.  and three dimensions (customers, products, region)

The dashed lines are many to many joins between fact tables using  intermediate tables. We need to be able to look at an order number, and see order amount, invoice amount and payment amount (payment comes from ledger). The problem is that we are not able to link invoices and payments (using an intermediate table) because this would introduce ambiguity between ledger and dimensions (customers, products, region).

Any idea how to go about this?

[v-jiascu-msft]

Hi @ConstMoss,

The relationships all depend on your data model. It seems the ledger has the details of invoices. If you can find out a compound primary key, there will no influence on other dimensions. For example:

Ledger:

RowID    InvoiceNumber  InvoiceRowID  Product  Payment

1            In001                  1                      A            100

2            In001                  2                      B            200

Invoice:

InvoiceNumber InvoiceRowID   Product   Amount

In001                 1                       A             10

In001                 2                       B              20

If the InvoiceNumber and InvoiceRowID can determine a unique row of each table, we can join two column to get a compound primary key.

1. Add a new column to Ledger:

RelationshipCol = CONCATENATE([InvoiceNumber],[InvoiceRowID])

2. Add a new column to Invoice:

RelationshipCol = CONCATENATE([InvoiceNumber],[InvoiceRowID])

3. Create relationship between these two columns. Only the relationship between Invoices and Middletable is bi-direction.

If this can't be a solution, please provide a sample in TEXT mode. The .pbix file would be great.

Best Regards!

Dale

[ConstMoss]

hi Dale (@v-jiascu-msft) - thanks for your response.

direct relationship between invoice and ledger is not possible so i am using a bridge table to connect them. i can work on putting togther a pbix file for you. can you think of any other solution?

[v-jiascu-msft]

Hi @ConstMoss,

Please share your .pbix file. You can upload it into the cloud driver then share the link here or through private messages.

Best Regards!

Dale

[ConstMoss]

Hi @v-jiascu-msft

i just uploaded the model and sent you the link using private message. please let me know if you need anyhting else. thanks for your help.