Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Employee Absence Rate

Hi,

 

I'm trying to calculate employee sickness absence rate, the end goal is to have a graph showing the absence rate for each month which will change when a different business area is selected. The calulcation will be Total Sickness Days Lost / Available Working Days (Sum of FTE X Working Days for the month)

 

I've started out with 4 tables.

 

  • Employee Staff List - Full List of employees for the previous 12 months
  • Sickness Absence - Full 12 month list of all employee absences
  • Working Days Table - A list of working days for each month.
  • Sector List - A list of all sectors

These are the relationships I have set up and I've created a table with what I'm hoping to achieve. When I change the DB Super Sector, it changes the FTE but not the total no of days sickness.  I'm sure it's a simple fix but I can't work it out.

 

Relationship overview.JPGManage relationship.JPGtable.JPG

 

 

6 REPLIES 6
Watsky
Solution Sage
Solution Sage

A couple of things I noticed:

 

  1.  You have a bi-directional crossfilter between Employee Info and Working Days. I'm assuming that Working Days is a look up table. I would change that to one direction. 
  2. Everything except Sector has a Many to Many relationship, which means the value you are joining on both sides have duplicates. I assume you are joing Working Days on both tables. Why are there duplicates values on the Working Days table?
  3. Have you tried joining Employee Info to Sickness and Absence using Emp ID?

Did my answer(s) help you? Give it a kudos by clicking the Thumbs Up!
Did my post answer your question(s)? Mark my post as a solution. This will help others find the solution.

Proud to be a Super User!

Anonymous
Not applicable

I'm afraid I'm unable to share the file due to the sensitivity of the data.

 

1. Would that be one directional from Employee Info to Working days?

2. What would I change it to?

3. What type of relationship would this be? Would this then filter all of the other matching columns?

  • Since you have a Many to Many relationship Power BI is going to ask you if you want filter Employee with Working Days or vice versa. 
  • If you were to keep your model the way you have it, then you would need other tables that had no duplicates. For instance, if you were joining on Employee ID between two tables then you would want a list of all distinct Employee IDs between those two tables.
  • Employee ID to Emp ID? I assume they are the same? As for the other tables I would use your Sickness Absence as your Fact table connecting Employee to it. Connecting the Working Days table to the Sickness Absence and Sector to Employee. 

 


Did my answer(s) help you? Give it a kudos by clicking the Thumbs Up!
Did my post answer your question(s)? Mark my post as a solution. This will help others find the solution.

Proud to be a Super User!

Anonymous
Not applicable

okay how do you calculated your measures?

Is it possible for you to share your pbix?

Anonymous
Not applicable

Thanks for the quick reply, the table in the slider is "Sector"

Anonymous
Not applicable

Hi

 

Which table are you using in the slider? Because you have that column in two tables.

If you are using the Sector table notice that you don't have an active relationship.

 

Please also provide you DAX measure.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.