cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper IV
Helper IV

Can we have multiple Fact table in Data Model

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.

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Advocate IV
Advocate IV

Re: 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.

Capture.PNG

View solution in original post

6 REPLIES 6
Highlighted
Advocate IV
Advocate IV

Re: Can we have multiple Fact table in Data Model

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.

Helper IV
Helper IV

Re: Can we have multiple Fact table in Data Model

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.

Highlighted
Advocate IV
Advocate IV

Re: Can we have multiple Fact table in Data Model

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.

Highlighted
Helper IV
Helper IV

Re: Can we have multiple Fact table in Data Model

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.

 

Highlighted
Advocate IV
Advocate IV

Re: 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.

Capture.PNG

View solution in original post

Highlighted
Helper IV
Helper IV

Re: Can we have multiple Fact table in Data Model

Thanks @belisqui 

Really appreciate your help.

Helpful resources

Announcements
Ignite

Microsoft Ignite

This will be a conference that you do not want to miss!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Don't miss the Power BI Dev Camp this week!

August Community Highlights

Check out a full recap of the month!

August 2020 CYST Challenge

Check out the winners of the recent 'Can You Solve These?' community challenge!

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

Top Solution Authors