how to relate multiple fact tables with shared dimensions?

[faerewatson]

I want to relate two fact tables and I have several shared dimensions. (the relationships to the dimensions are single direction, one to many, active) I have an ID field on both fact tables that I could use, but I cannot make the relationship active as it creates a circular reference so Power BI won't allow. Any suggestions? 

[v-yulgu-msft]

Hi @faerewatson,

Since you have created one to many relationship between FactA and DateKey, and between FactB and DateKey, there existing an indirect many to many relationship between FactA and FactB. The error message has pointed out that the one to many relationship between FactB and FactA based on [PrescriptionID] will introduce ambiguity between Tables. If you want to active this unsupported relationship, the only way is to inactivate other relationships.

Best regards,
Yuliana Gu

[faerewatson]

Thanks @v-yulgu-msft - can you explain what an inactive relationship is for? What does that buy me?

[v-yulgu-msft]

Hi @faerewatson,

To active the relationship based on [PrescriptionID], you may need to inactive other one to many relationships between fact table and dimention tables. For example, the relationship between FactA and DimMember based on [MemberKey].

Regards,
Yuliana Gu