Forum Discussion
Best 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]
)
)
Hi 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]
)
)
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?
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.