Forum Discussion
PowerBI Relationships
I’m working with two datasets in Power BI that both contain multiple rows per employee, but represent different types of information.
Dataset 1: Combined Headcount Data
Every month, I load a headcount report.
It includes:
Employee ID
Gender
Age
Ethnicity
Position / Job level
Salary
Agency (this is large marketing company with multiple agencies)
Because I append monthly files together:
Each employee appears once per month
Therefore, the table contains multiple rows per Employee ID
Example:
Employee ID 12345 appears in January, February, December, etc.
Dataset 2: Campus (Training) Data
Contains training/course records
Each row represents one course taken
Employees can appear multiple times
Example:
If Employee 12345 took:
Excel101
PowerBI101
PowerPoint101
They will appear three times in this dataset.
What I’m Trying to Achieve
My dashboard has three sections:
1. Headcount Section (Independent from Campus)
Has a Month slicer
Has an Agency slicer
If I select January, it should show:
Headcount for January only
Headcount by Agency for January
This section works correctly and should remain independent from Campus data.
2. Campus Section
Shows courses taken
Needs:
Month slicer
Agency slicer
If I select:
January + Agency A
→ It should show how many courses were taken by employees in Agency A in January.
3. Demographics / Employee Info (Based on Campus Students)
This section should show demographics of employees who took training, such as:
Age group
Ethnicity
Job level
Agency
But filtered by:
Selected Month
Selected Agency
The Issue
When I try to create a relationship between Campus and Headcount, Power BI only allows many-to-many because both tables have multiple rows per Employee ID.
If I use that many-to-many relationship:
When I select January in a slicer
My demographics section shows the total January headcount
Instead of only the employees who actually took training in January
What I’m Trying to Achieve
When I select:
Month = January
Agency = X
I want to see:
Courses taken in January
And demographic info (age group, ethnicity, job level)
But only for employees who took training in that selected month
My Question
What is the correct way to model this?
Should I avoid directly relating the two fact tables?
How do I structure the relationships so that Month filtering correctly limits demographics to only Campus participants?
Right now, the many-to-many relationship is giving me incorrect filtering behavior.
What is the recommended modeling approach for this scenario?
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
7 Replies
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.
Hi b-kopik,
Thank you for reaching out to the Microsoft Fabric Community Forum. Also, thanks to tanishabhawsarr, Rupa01, for those inputs on this thread.Has your issue been resolved? If the response provided by the community member tanishabhawsarr, Rupa01, addressed your query, could you please confirm? It helps us ensure that the solutions provided are effective and beneficial for everyone.
Hope this helps clarify things and let me know what you find after giving these steps a try happy to help you investigate this further.
Thank you for using the Microsoft Community Forum.Thanks all for the suggestions! Here are some screen captures - all the relationships are active and working.
Except for the one highlighted:
Basically - The Business Unit is a table with only one column with all business units. There are no duplications. That is one of my slicer. So I don't understand why I can't connect it to the terminations table (to the business unit column in that table)?
Every time I try to make this relationship active, I get this msg:
Hi b-kopik,
The error is expected because Power BI detects two active filter paths between Business Area and Business Units. This creates an ambiguous relationship path, which is why Power BI forces one relationship to remain inactive.
A star schema should not have circular or ambiguous filter paths. Follow single-direction 1-to-many relationships from dimensions to facts to maintain a clear and predictable filter flow. If multiple filter paths are needed, use a bridge table instead of bi-directional relationships to avoid ambiguity and keep the model clean and scalable. Microsoft's modelling guidance recommends this approach for both performance and usability.
Recommendation -
- Model Business Area, Business Units, Agencies, and Networks as dimension tables.
- Keep fact tables such as Combined Headcount and Havas - Termination Information separate.
- Avoid dimension-to-dimension relationships through fact tables.
- If Business Area and Business Units genuinely require a many-to-many connection, introduce a bridge table instead of creating multiple filter paths.
References -
- Understand star schema and the importance for Power BI - Power BI | Microsoft Learn
- Bi-directional relationship guidance - Power BI | Microsoft Learn
💡 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
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 EngineerAmazing thanks so much!
Hi b-kopik ,
Could you please let us know whether the solution provided above helped in resolving the issue?
Thanks!!
