Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Ambiguous Paths two tables referencing the same

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 tab...
Need Help
Tutorial Requests
Cmcmahan's avatar
Cmcmahan
Resident Rockstar
7 years ago

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 

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    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's avatar
      Cmcmahan
      Resident Rockstar
      7 years ago

      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?

      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago

        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