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, 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
Community SupportHi b-kopik , Thank you for reaching out to the Microsoft Community Forum.
Your model is correct. The issue is simply that filters don’t flow from one fact table to another in a star schema. So, when you average Age from Headcount, it’s filtered by Month and Agency, but it isn’t automatically limited to employees who took training. You need to explicitly pass the filtered Employee IDs from Campus into Headcount. Please try something like:
AvgAgeTraining :=
CALCULATE (
AVERAGE ( 'Combined Headcount'[Age] ),
TREATAS (
VALUES ( 'Campus Data'[Employee ID] ),
'Combined Headcount'[Employee ID]
)
)
This forces Headcount to calculate only for employees who exist in Campus for the selected Month and Agency. If this still doesn’t work, can you please confirm that your relationships are single direction (dimension -> fact), whether Age is monthly or static and can you please share a small sample of both tables for one problematic month so the behaviour can be reproduced precisely. Also, share any other relevant details. Do not include sensitive information. Do not include anything that is unrelated to the issue or question.
Need help uploading data? https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...
- b-kopik
Thanks, 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?
- v-hashadapu
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]
)
)- 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?