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]
)
)
Helper IIIThanks, it worked!
I have a new challenge with my model. My Campus (training) dataset doesn’t include Agency, but my Headcount dataset does, and employees can move between agencies over time (so the headcount dataset is the most up to data). I want the Campus section of my dashboard to filter by Month and Agency, showing how many employees took courses based on the agency they belonged to at the selected month. For example, if I select January + Agency A, it should count only employees who were in Agency A in January, even if they later moved to another agency. What’s the best way to model this in Power BI so I can link the datasets show the correct agency per selected date - even though agency is only in the headcount data? Is it not enough to link employee ID and it will tell me the agency as of that month?
Community SupportHi b-kopik , Thank you for reaching out to the Microsoft Community Forum.
Linking only by Employee ID is not enough in this case, because Agency is a time dependent attribute. An employee can belong to different agencies in different months, so the correct agency must come from the Headcount snapshot for the selected month. The usual approach is to let the Month and Agency slicers filter the Headcount table first, which gives you the employees who belonged to that agency in that month and then pass that employee set to the Campus table when calculating training metrics.
For example:
EmployeesWithTraining :=
CALCULATE (
DISTINCTCOUNT ( 'Campus Data'[Employee ID] ),
TREATAS (
VALUES ( 'Combined Headcount'[Employee ID] ),
'Campus Data'[Employee ID]
)
)
- b-kopik
Thanks. For other instances, should I create an agency table and use that as a slicer so I can combine different table and have agency as my primary key?
- v-hashadapu
Hi b-kopik ,
Yes, do that. But, don’t treat Agency as a key to join tables. Let it filter Headcount (where Agency is time-valid), then use your existing TREATAS pattern to push the correct employees into Campus.