Forum Discussion

b-kopik's avatar
b-kopik
Helper 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
Helper III
4 months ago

I did this and it worked. But now I am trying to connect two columns with the agency information. And I keep getting this msg: 

 

v-hashadapu's avatar
v-hashadapu
Community Support
4 months ago

Hi b-kopik , Thank you for reaching out to the Microsoft Community Forum.

 

Yes, the date format you used is correct, so you’re good there.

 

That new error is happening because you’re trying to relate Agency columns that both have duplicate values, which forces a many-to-many relationship. In your model, you shouldn’t connect Agency directly between tables anyway. Instead, create a small Agency dimension (distinct list of agencies) and relate it one-to-many to Headcount (and use it in slicers). Since Agency is time-dependent and only reliable in Headcount, let it filter Headcount first and then use your existing TREATAS logic to pass the correct employees into Campus. This keeps the model clean and avoids the same filtering issues coming back.

  • b-kopik's avatar
    b-kopik
    Helper III
    4 months ago

    Thanks, that makes sense on the Agency table and avoiding the many-to-many issue.

    I haven’t actually used TREATAS before though, so I’m a bit stuck on that part. Could you show me how you’d set that up in this case or what the measure would look like? 

  • v-hashadapu's avatar
    v-hashadapu
    Community Support
    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]
    )
    )