Forum Discussion
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.
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.
7 Replies
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.
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.
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.