Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago

Help with shared dimension between two fact tables

Hi.

I have tried looking for an answer but haven't found one.

 

Let's say I have 2 fact tables: Customers and Sales.

Both of these share a common dimension called SalesPersons.

But, it may not be the same SalesPerson, I mean, a Customer may have a SalesPerson asigned, but a Sale may be made by another SalesPerson.

 

PowerBI won't allow me to use the same SalesPersons dimension for the 2 fact tables.  The only workaround that I found was to make a copy of the SalesPersons table with another name, like CustomersSalesPersons.

 

Is this the rght/only way?

I'd like to know, because SalesPersons has a herarchy of 2 more tables above.

 

Thanks

3 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    8 years ago
    What error or problem are you seeing connecting the common dimension table to your 2 fact tables? Your work-a-round is fairly common but interested in the issue you are seeing.
    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      The relationship on the 2nd fact table remains Inactive, when I try to active it says: "You can't create a direct active relationship between Sales and SalesPersons because an active set of indirect relationships already exists between these tables. To make this relationship active, set the Cross filter direction to 'single', delete or deactivate any of the indirect relationships first."

  • v-yulgu-msft's avatar
    v-yulgu-msft
    Microsoft Employee
    8 years ago

    Hi Anonymous,

     

    If it needs at least two columns to uniquely identify the mapping relationship between tables, I think currently the best choice is to combine the two column like CustomersSalesPersons, then, generate relationship based on this combined column.

     

    Regards,

    Yuliana Gu