Common Date filter for Multiple Tables without creating ambiguous relationships?

[JanBuzek]

Hello everyone,

I am facing a situation where I have the following tables and structure:

orders (ID, OrderDate)

orderitems (ID, order_ID, product, quantity_ordered)

deliveryitems (ID, orderitems_ID, delivery_ID, quantity_delivered)

deliveries (ID, DeliveryDate)

date (Date)

I am trying to compare running totals of Orders (quantity_ordered by OrderDate) and Deliveries (quantity_delivered by DeliveryDate).

As I have dates in two different tables, I created a dedicated date table in order to use it for an axis. However, the problem I run to is that I cannot make the relationships between dates in tables 'date' and 'deliveries' since it would create ambiguity.

How can I solve this? 

Thank you for your help, I feel stuck.

Jan

[v-yuta-msft]

Hi JanBuzek,

What's the relationship between the two tables and could you share some sample data?

Regards,

Jimmy Tao

[JanBuzek]

Hello Jimmy, this is the structure I am working with.
I am not sure what kind of sample data would help. The tables keep the following data:

• orders: general information about the order (date, customer....)
• orderitems: individual items for each order (product, price....)
• delvieryitems: information about delivery of each orderitem - one orderitem can be delivered in multiple deliveries and a different quantity than was ordered can be delivered
• deliveries: general information about a delviery (date)
• invoices: general information about the related invoice - yes, this could probably be merged with deliveries since it is a 1-1 relationship

As you can see creating a Date table tied to both orders and invoices leads to an ambiguous relationships which Power BI does not want to let me create.

Thank you for your help.

Jan

[Greg_Deckler]

You have two options. You could create a second calendar date table to use with Deliveries. Or, you could create an inactive relationship between your date table and Deliveries table and use USERELATIONSHIP.