Forum Discussion
PowerBI Relationships
Hi b-kopik,
In this scenario, I would avoid directly relating the two fact tables (Headcount & Campus) using a many-to-many relationship. Since both tables contain multiple rows per Employee ID, a direct relationship can create ambiguous filtering and incorrect results.
A better approach is to model this as a star schema, using shared dimension tables. Microsoft also recommends organizing Power BI models with fact and dimension tables for better filtering, usability, and performance.
Refer - Understand star schema and the importance for Power BI - Power BI | Microsoft Learn
✔️Correct Approach for creating relationships -
DimEmployee[Employee ID] 1-* FactHeadcount[Employee ID]DimEmployee[Employee ID] 1-* FactCampus[Employee ID]DimDate[Date] 1-* FactHeadcount[Month/Date]DimDate[Date] 1-* FactCampus[Course Date]💡 Helpful? Give a Kudos 👍 — keep the community growing
✅ Solved your issue? Mark as Solution ✔️ — help others find it faster
Best regards,
Rupasree Achari | BI & Fabric Analytics Engineer
The correct approach is to avoid a direct many-to-many relationship between the two fact tables.
Instead, create a star schema:
DimEmployee 1 ─── * FactHeadcount
DimEmployee 1 ─── * FactCampus
DimDate 1 ─── * FactHeadcount
DimDate 1 ─── * FactCampus
- FactHeadcount = monthly employee snapshots.
- FactCampus = training/course records.
- DimEmployee = one row per Employee ID.
- DimDate = shared calendar table.
Then build your demographics visuals using fields from DimEmployee and measures from FactCampus (e.g., DISTINCTCOUNT(FactCampus[EmployeeID])).
This way, when you select January + Agency X, you'll see demographics for only the employees who took training, not the entire January headcount.