Forum Discussion
Can we have multiple Fact table in Data Model
That's probably because of the cross filter direction - both. You need to ensure your relationships between Dim and Fact are not overlaping with relationships between Fact and Fact.
In the example below I wouldn't be able to create an active relationship between occurences AND incidents (running) because there is already a link connecting Incident to Occurrence.
Thanks belisqui for your reply.
Please advise, if there are some common dimensions for Eg. Calendar Date, which is related to each fact table. Then should we duplicate these dimension to use with each fact or make it a common dimension for each fact.
What should be the best way.
We have 3 common dimensions between each fact tables.
Thanks and regards.
No need for duplication. You can have a single Date Dimention and conect it to multiple fact tables. Just be aware that when you filter the dimension date you will be filtering both fact tables. You can manage that in the "Relationships" are in power bi.
Thanks belisqui for your response.
One last question,We have 8 fact tables and 3 common dimensions. When i use these common dimensions with each fact table, i get the ambiguity error. It does not allow me to create active relationships.
Should i keep these relationships as Inactive and make it active using DAX calculation whenever required.
Many thanks for your help again.
That's probably because of the cross filter direction - both. You need to ensure your relationships between Dim and Fact are not overlaping with relationships between Fact and Fact.
In the example below I wouldn't be able to create an active relationship between occurences AND incidents (running) because there is already a link connecting Incident to Occurrence.
Thanks belisqui
Really appreciate your help.