Forum Discussion
Join 3 fact table together with one-to-many relationship
I have a large semantic model, and as a beginner, I'm trying to understand the best practices for data modeling in Power BI.
In my current model, the data engineer has connected two to three fact tables using one-to-many relationships. They mentioned that this design will not impact reporting or performance in Power BI. However, I'm still skeptical because I've often read that fact-to-fact relationships are generally discouraged.
Is it considered a good practice to connect multiple fact tables directly with one-to-many relationships? If not, what are the potential drawbacks? Also, what changes or recommendations should I discuss with the data engineer to redesign the semantic model according to best practices?
Hi Anushree2929
Your instinct is right, don't connect fact tables directly in Power BI. Use a star schema instead.
Why fact-to-fact is not recomended on your approach:
- Wrong totals – different grains cause row duplication and inflated sums.
- Confusing filters – filter direction gets unpredictable.
- Slower – Power BI's engine is built for star schemas.
- Hard to maintain – nobody trusts the numbers as the model grows.
The recomended workaround: Star Schema:
- Facts connect only to dimensions (Date, Product, Customer…).
- Facts never connect to facts.
- Relationships: one-to-many, single direction, Dim → Fact.
- Compare facts (Sales vs. Budget) through shared dimensions.
Dim_Date ─┬─► Fact_Sales
├─► Fact_Budget
└─► Fact_Inventory
Dim_Product ┘ (shared across all)
What to discuss with your data engineer:- Build shared dimensions (Date, Product, Customer).
- Remove fact-to-fact relationships.
- Connect each fact to the shared dimensions.
- Keep relationships single-direction.
- Compare a cross-fact measure before/after old totals were likely inflated.
Exception: header/detail like Sales_Header → Sales_Lines is okay.
Reference links: Start Schema Guidance
Thanks,
Srikanth Cheri.
4 Replies
Hi Anushree2929
Your instinct is right, don't connect fact tables directly in Power BI. Use a star schema instead.
Why fact-to-fact is not recomended on your approach:
- Wrong totals – different grains cause row duplication and inflated sums.
- Confusing filters – filter direction gets unpredictable.
- Slower – Power BI's engine is built for star schemas.
- Hard to maintain – nobody trusts the numbers as the model grows.
The recomended workaround: Star Schema:
- Facts connect only to dimensions (Date, Product, Customer…).
- Facts never connect to facts.
- Relationships: one-to-many, single direction, Dim → Fact.
- Compare facts (Sales vs. Budget) through shared dimensions.
Dim_Date ─┬─► Fact_Sales
├─► Fact_Budget
└─► Fact_Inventory
Dim_Product ┘ (shared across all)
What to discuss with your data engineer:- Build shared dimensions (Date, Product, Customer).
- Remove fact-to-fact relationships.
- Connect each fact to the shared dimensions.
- Keep relationships single-direction.
- Compare a cross-fact measure before/after old totals were likely inflated.
Exception: header/detail like Sales_Header → Sales_Lines is okay.
Reference links: Start Schema Guidance
Thanks,
Srikanth Cheri.Hi Shrikanth,
Thanks for your response, the dataset is huge and for business needs data engineer is suggesting to use snowflake schema.
Hi Anushree2929
We would like to inquire whether have you got the chance to check the solutions provided above in commiunity to resolve the issue. We hope the information provided helps to clear the query. Should you have any further queries, kindly feel free to contact the Microsoft Fabric community.Hi Anushree2929
We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. And, if you have any further query do let us know.
Thank you.
