Forum Discussion

b-kopik's avatar
b-kopik
Icon for Helper III rankHelper III
6 months ago
Solved

Best Data Model for Headcount (Monthly) and Course Data in Power BI

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 l...
Need Help
  • v-hashadapu's avatar
    v-hashadapu
    4 months ago

    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]
    )
    )

b-kopik's avatar
b-kopik
Icon for Helper III rankHelper III
6 months ago

Thanks, 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's avatar
johnt75
Icon for Super User rankSuper User
6 months ago

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's avatar
    b-kopik
    Icon for Helper III rankHelper III
    6 months ago

    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

    • v-hashadapu's avatar
      v-hashadapu
      Icon for Community Support rankCommunity Support
      6 months ago

      Hi 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's avatar
        b-kopik
        Icon for Helper III rankHelper III
        6 months ago

        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?

    • johnt75's avatar
      johnt75
      Icon for Super User rankSuper User
      6 months ago

      You could try

      AvgAgeTraining =
VAR EmployeesWithTraining =
    CALCULATETABLE ( VALUES ( 'Campus Data'[Employee ID] ) )
VAR Result =
    CALCULATE (
        AVERAGE ( 'Combined Headcount'[Age] ),
        TREATAS ( EmployeesWithTraining, Employee[Employee ID] )
    )
RETURN
    Result