Can we have multiple Fact table in Data Model

[Anonymous]

Hi All,

As per our requirement, we need to use three unrelated fact tables, basically different data models. We want to create three pages in our Power BI report, one page for each data model.

Please let me know what are the option available 

Thanks.

[belisqui]

That's completely fine. There is no issues with adding multiple unrelated fact tables to Power BI. You don't need to follow any formal methodology.

Ideally you may have a Date Dimension collecting them all, but they can be completely indepentent if you want.

Thumbs up if that answers your question.

[Anonymous]

 I got some requirement that i needed to bypass a page level filter like Company ID from the fact table  will come dynamically from the web  and i have to find the region from which company id belongs to and then show all the companies associated to that region irrespective of what id user has passed.

To do this i made another fact table took the selected company id value from a measure and find out the region of that id  and then filtered my new model  with that and so bypass the page level filters.

So i have 2 fact tables one wil all tables and one with some required tables and i guess based on your replay i can say my approach is correct as its not formal to have multiple fact tables


Just reply your thought or any source which says this is okay.

[Anonymous]

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.

[belisqui]

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.

[Anonymous]

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.

[belisqui · Accepted solution]

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.

[Anonymous]

Thanks @belisqui 

Really appreciate your help.