Ambiguous Paths two tables referencing the same

[RyanRFC]

Hi I have two places in my model where this is happening.

I have one table called Suppliers which is just a list of supplier names, and then I have two tables that need to pull data from that table. Bookings and AddOns. Bookings has a column for Suppler ID, and Addon also has one too. 

So in a travel package, some customer might get a cruise (booking) from X supplier and another cruise (booking) from Z supplier, and might buy flights (addon) from Y supplier. The thing is that suppliers provide addons and bookings.

Here is another example. I have a table ASPNetUsers, that has first and last names that we need to reference in two other tables, Lead and CBI (like a travel package). In lead, theres a column called assigned to, and it looks to the ASNNetUsers table to bring in their name, but once that lead is converted to a sale, it generates a record in CBI (1 to 1 relationship). The CBI also has a column called assigned to, and it needs to reference the ASPNetUsers. A lead might be assigned to PersonA, but once its converted it might be assigned to PersonB.

How do we manage these relationships? Here is a diagram.

[Cmcmahan]

In PowerBI you can't enforce both relationships at the same time because there are circular dependencies.  However, when you want to use the non-active relationship in a DAX query, you can use the USERELATIONSHIP function to temporarily use the other relationship 

[RyanRFC]

Thanks @Cmcmahan thats sheds some light on the situation, however I have to use that with Calculate and what I really want to do is bring Supplier Name into AddOn.. or bring UserName into CBI/Lead... it seems I can only use Calculate with numerical columns.

[Cmcmahan]

You can definitely use CALCULATE on text columns.   For example, you can use 

CALCULATE( SELECTEDVALUE(Table[MyTextField]), USERELATIONSHIP(Table[Date], DateTable[Date])) 

To determine the value of MyTextField for a specific date.

Also, when you say you want to "bring SupplierName into AddOn", what do you mean?   As a calculated column? As a measure?  Just as a value you have available when you need it?

[RyanRFC]

Im not sure what I mean so Ill just describe...

Usually a supplier just sells one type of product, either A) a 'booking class' (itinerary based) product like a cruise/lodge or B) one-offs like flights, hotels (non-itinerary based) which we call 'addons' . Lets take for example some supplier, Royal Galapagos. They are a Supplier that can provide both types - 'bookings' (cruises/lodges) as well as hotel services, and transport (addons).

AddOns table just has description, sale price, and supplier ID. Bookings derive their description/prices from other tables so they are more complex. But they make the link to Supplier table through Itinerary ->Property -> Supplier.

So on some reports we might select one sale, to see what was sold. So like this:

Betty Sue Johnson

- 3 Night Cruise - Royal Galapagos

- Land Transfer - Royal Galapagos

- Flights - American Airlines

- 4 Night Amazon Cruise - Delfin Amazon Cruises

On other reports we want to aggregate, how much we sold per one suppliers, so for that it would need to go the other direction. And those amounts would need to aggregate from both Addon and Booking tables.

2019

-Delfin Amazon Cruises - $320,000

-Royal Galapagos - 1.2 mil

-American Airlines - 245,000

@Cmcmahan we appreciate the direction kind stranger