Forum Discussion
Helper IIIBest Data Model for Headcount (Monthly) and Course Data in Power BI
Hi b-kopik ,
If you want to count how many courses were taken, try something like:
CoursesTaken :=
CALCULATE (
COUNTROWS ( 'Campus Data' ),
TREATAS (
VALUES ( 'Combined Headcount'[Employee ID] ),
'Campus Data'[Employee ID]
)
)
or if you want number of employees trained, try something like:EmployeesTrained :=
CALCULATE (
DISTINCTCOUNT ( 'Campus Data'[Employee ID] ),
TREATAS (
VALUES ( 'Combined Headcount'[Employee ID] ),
'Campus Data'[Employee ID]
)
)
Super UserNever create a relationship between fact tables, that goes completely against the star schema which is the best way to model data. Instead create dimension tables which have one-to-many relationships to your fact tables, this is much more efficient and it is much easier to manipulate the filters in the way that you want to.
As a minimum you will want a date dimension and an employee dimension, both linked to both your fact tables. You can create an employee dimension by retrieving a list of unique employee IDs. You can do this in Power Query or DAX, the only thing you need to be mindful of is whether or not it is possible for an employee ID to appear in the training data and not in the headcount data. If it is not possible then you can create an employee dimension with a simple DISTINCT(Headcount[EmployeeID] ). If there might be employees in training who are not in headcount then you could do something like
Employee =
DISTINCT (
UNION (
DISTINCT ( Headcount[Employee ID] ),
DISTINCT ( Training[Employee ID] )
)
)
Once you have the employee dimension, link it to both headcount and training.
You could also create dimensions for e.g. Agency, Gender, Ethnicity, Course etc, again by using DISTINCT. If you do create these extra dimensions, make sure that you use the dimension in all your visuals and filters.
You can now create a measure to show the number of employees who did training in the chosen month like
Num employees with training =
CALCULATE ( COUNTROWS ( Headcount ), Training )
Because this is counting from Headcount, you can break it down by any of the columns in that table, or by dimensions you have chosen to extract from that table. It works by using the expanded table of Training, so only employees who have rows in the Training table for the given month will be considered.
One thing to bear in mind is that this method needs to have only 1 month chosen in the slicer. If more than one month is chosen then employees will be counted multiple times. You can enable single select on the slicer to ensure that only 1 month can be chosen.
b-kopikThanks, I’ve now remodelled this into a proper star schema as suggested.
I created an Employee dimension (distinct Employee IDs) linked one-to-many to both fact tables
There is no longer any direct relationship between the two fact tables.
Section 1 (Headcount) works correctly.
Month + Agency slicers correctly show headcount for the selected month.Section 2 (Training/Campus) also works correctly.
Month + Agency slicers correctly show number of courses taken.However, Section 3 (Demographics of employees who took training) is still not behaving correctly.
When I select:
Month = January
Agency = X
The demographics visuals (Age group, Ethnicity, Job level, etc.) are still showing the total January headcount, not just the employees who actually took training in January.
So although the model now follows a star schema and filtering works independently for each fact table, I’m not getting the intersection behavior I need for the demographics section.
Am I correct in thinking I now need a specific measure (rather than relying on implicit filtering) to restrict Headcount to only employees that exist in Training for the selected month?
If so, what is the recommended pattern to achieve this without reintroducing many-to-many behavior?
- johnt75
Yes, you need to write an explicit measure. I included an example, and an explanation of how it works, in my original post but it was quite a long post. Have another look at it from the second code snippet down.
- b-kopik
Thanks, I am having trouble with the Age metric. I tried both formulas and they didn't work:
Option 1:
AvgAgeTraining =
CALCULATE(
AVERAGE('Combined Headcount'[Age]),
'Campus Data'
)Option 2:
AvgAgeTraining =
CALCULATE(AVERAGE('Combined Headcount'[Age]), 'Campus Data')
I would like for it to only calculate the average age of those employees who attended the training - but the age column is in the combined headcount dataset