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 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.

Need Help
Tutorial Requests

4 Replies

  • 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?