Issues with filtering - sickness data/absence rate

[NPEQ]

I am creating a Power BI Dashboard which will provide analytics around sickness absence in my company. I currently have three tables:

• AbsencesTable – this contains a list of all sickness absences from the past few years. Each row is an invidual day of absence. Each row has the employee ID (Payroll No.) the number of Days Lost, and a few other fields.
• EmployeeData – this contains a list of employees past and present, including their department, their start date, and their end date (which can be blank)
• DatesTable – a list of unique dates from within the model. 

One of the initial issues I had was calculating absence rates, as this requires me to know the 'Available Days' which is not a figure which my raw data contains. To combat this, I created a new column on DatesTable which was a SUM of the FTE for each employee on that particular date, if they were employed on said date, and if said date was a working day (NETWORKDAYS). Not perfect, but close enough.

I also added a number of other columns to the DatesTable, including AbsenceDays (calculated from AbsencesTable), Headcount (also calculated from EmployeeData) and FTE (also calculated from EmployeeData).

In terms of relationships:

* EmployeeData to AbsencesTable - bidirectional, active, One to Many. Joined on the payroll number.

* DatesTable to AbsencesTable - bidirectional, active, One to Many. Joined on the date field.

Most of my visualisations work fine when looking at the company as a whole. The issue I'm having is filtering by department, which is a key ability I need the dashboard to be able to do. It works for some statistics, but specifically does not work for absence rate, headcount and FTE, and I think the issue is that all three of those figures on DatesTable are being calculated using EmployeeData, and no relationship exists between these two tables.

I have tried creating a sort of staging table between DatesTable and EmployeeData to try and establish a relationship, but to no avail. There may be a really simple solution I am missing, or an easier alternative way to calculate the problematic statistics I mentioned, but I'm a bit stuck at present. Any help appreciated!

[bhanu_gautam]

@NPEQ 

you can try the following steps:

Create a relationship between DatesTable and EmployeeData based on the department field. This will establish a connection between the two tables and allow you to filter by department.

Once the relationship is established, you can update the calculations for absence rate, headcount, and FTE in the DatesTable to consider the department filter. You can use the RELATED function in DAX to retrieve the department information from the EmployeeData table.

For example, to calculate the headcount for a specific department on a particular date, you can use the following formula in the DatesTable:

Headcount = CALCULATE(COUNT(EmployeeData[EmployeeID]), RELATED(EmployeeData[Department]))
Similarly, you can update the formulas for absence rate and FTE to consider the department filter.