Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Anonymous
Not applicable

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

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

7 REPLIES 7
belisqui
Advocate IV
Advocate IV

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
Not applicable

 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
Not applicable

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.

Anonymous
Not applicable

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.

Capture.PNG

Anonymous
Not applicable

Thanks @belisqui 

Really appreciate your help.

Helpful resources

Announcements
Sept PBI Carousel

Power BI Monthly Update - September 2024

Check out the September 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Sept NL Carousel

Fabric Community Update - September 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors