Help with shared dimension between two fact tables

[pedropimentel]

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

[v-yulgu-msft]

Hi @pedropimentel,

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

[Greg_Deckler]

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.

[pedropimentel]

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