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

Hi b-kopik 

order to get this dashboard working correctly, what needs to be done is to transition from a direct many-to-many relationship between your two data tables to a star schema structure with shared dimensions. The current issue exists because Power BI cannot effectively filter the Headcount table using the Campus table when an Employee ID appears in multiple months across both; this is why selecting January shows you the entire headcount instead of just the trainees. To fix this, the approach involves creating a unique Employee table and a dedicated Calendar table that both connect to your existing data. For the Employee table, you can use a DAX calculated table like Employees = DISTINCT(UNION(SELECTCOLUMNS('Headcount', "ID", 'Headcount'[Employee ID]), SELECTCOLUMNS('Campus', "ID", 'Campus'[Employee ID]))) to ensure every ID is represented only once. Similarly, a Calendar table should be generated using Calendar = CALENDARAUTO() so that your Month slicer comes from a single, neutral source.

Once these tables are created, you should connect the Employee ID from the new Employee table to both the Headcount and Campus tables with a one-to-many relationship, and do the same for the Date or Month columns from the Calendar table. When the slicers for Month and Agency are pulled from these new shared dimension tables, they will filter both fact tables simultaneously and accurately. To ensure the Demographics section specifically isolates only those who took training in the selected month, what needs to be done is to create a simple DAX measure such as IsParticipant = IF(ISBLANK(COUNTROWS('Campus')), 0, 1) and add it to the "Filters on this visual" pane for your demographic charts, setting the value to 1. This logic forces the demographic visuals—which pull their descriptive data like Age and Ethnicity from the Headcount table—to only display rows where a corresponding training record exists in the Campus table for that specific month and agency. This structure keeps your Headcount section independent while allowing the Campus and Demographics sections to sync perfectly without double-counting or over-reporting.

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly