Best Practice when needing 2 relationships

[AnthonyH28]

Quick Background: Our company has two customer tables: DIM_Customer and DIM_CustomerShipTo The Customer dimension is all of the base Customers while the CustomerShipTo table contains all of the Ship To locations that relate to each Customer. Each customer gets their own unique number but the ShipTo field is just 1-whatever but they always relate back to the Customer number.

Example: Customer 1 = 10 and has 5 ShipTo's, 1 thru 5. Each a different location that the base customer ship's to. I currently have the relationships setup to reference the main customer number from DIM_Customer to DIM_CustomerShipTo and Customer to Fact_Orders.

My problem is that if I pull in Customer & CustomerShipTo from the DIM tables and the sales from FACT_Orders, that im getting the sales value duplicated for every ShipTo the Customer has. Even though the FACT_Orders HAS the ShipTo field and it should match, PBI is not able to make the connection that I really need 2 relationships: I need the Customer and CustomerShipTo.

How would I go about getting it to work so that it also makes the association with the FACT_Orders that the ShipTo is also related/important to the Customer number? I come from a SQL background so this is super simple in there with relationships but in here its a bit trickier.

[RMB · Accepted solution]

This would require you create a calculated column that concatenates the customer and customerShipTo columns within the FACT_Orders and DIM_Customer tables and then create the relationship on that column because Power BI cannot create a relationship on multiple columns at once.

If CustomerShipTo isn't located in the DIM_Customer table then the relationship order would need to be FACT_Order <-> DIM_CustomerShipTo <- DIM_Customer with the above concatenation switched.

[AnthonyH28]

I think you understood. I was trying to look elsewhere around the web and I think you more succinctly nailed what I would have to do in PBI. Appreciate it!