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...
  • 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
2 months ago

Hello,

 

I'm getting an ambiguous path error in Power BI and I'm trying to understand the best way to structure my model.

My data

I have four datasets:

1. Master Headcount table

  • Employee ID
  • Employee information
  • Agency
  • Business Unit
  • Business Area

This contains all active employees and is my primary headcount table.

2. Organizational hierarchy table

  • Agency
  • Business Unit
  • Business Area

This table contains only the organizational structure and no employee-level data.

3. Terminations table

  • Employee ID
  • Employee information
  • Agency
  • Business Unit
  • Business Area
  • Termination details

4. Promotions table

  • Employee ID
  • Employee information
  • Agency
  • Business Unit
  • Business Area
  • Promotion details

What I'm trying to achieve

I want users to be able to select an Agency, Business Unit, or Business Area from slicers and have all visuals update accordingly, including:

  • Current Headcount
  • New Joiners
  • Terminations
  • Promotions

For example, if a user selects a specific Business Area, all charts should automatically filter to that Business Area across all datasets.

The issue

When I create relationships between these tables, Power BI throws an error saying there are ambiguous paths between Business Area and Dates (or other dimensions). It appears there are multiple relationship paths connecting the same tables through different fact tables, and Power BI doesn't know which path to use for filtering.

My question

What is the recommended model design for this scenario?

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

Hi b-kopik , hope you are doing well. 

Can you please create a new post here in the community detailing this new issue? that way it will get noticed better and you will be able to get better suggestions. Also, others with similar issues may follow it better that way.

Thank you.