Distinct count of employees who have taken a leave of more than 10 Days (Personal Leave & Vacation)

DAX DAX Calculations
1

Hello,

I am trying to calculate a certain visual and I have been struggling with a similar case so I was wondering if somebody would be able to help me figure out the issue here. I have created a sample.

I have the tEmployee table,

image
image821×547 21.1 KB

The totals days is calculated based on the start and end date and the tDate(calendar) table.

I have another table, the t_Employee_Attendance,

image
image1264×461 39.4 KB

The Personal Leave and Vacation Column is calculated as below:
Personal Leave & Vacation = IF(t_Employee_Attendance[Reason] = “Personal Leave” || t_Employee_Attendance[Reason] = “Vacation”, t_Employee_Attendance[EmpID], BLANK())

Illness is calculated as,
Illness = IF(t_Employee_Attendance[Reason] = “Illness”, t_Employee_Attendance[EmpID], BLANK())

And Days Absent is calculated as,
Days Absent =
CALCULATE(
COUNTROWS(tDate),
DATESBETWEEN(tDate[Date],
t_Employee_Attendance[Absent From],
t_Employee_Attendance[Absent To]),
tDate[Count] = 1,
ALLSELECTED(‘t_Employee_Attendance’))

Afterwards, I created a measure to calculate sums of days absent for each employee with a personal leave or vacation,
Days Absent(PL/VAC) = CALCULATE(SUM(t_Employee_Attendance[Days Absent]),FILTER(t_Employee_Attendance,t_Employee_Attendance[Reason] = “Personal Leave” || t_Employee_Attendance[Reason] = “Vacation”))

I am trying to currently create another measure that would count the employee is they have taken off more than 10 days overall but it does not seem to be accurate. So, I thought of breaking it down in a table to see where the issue might be in and it turns out that it does not even take that employee in the count as the image below.

image

Can somebody please let me know if there is something I might not be doing accurately here? I am posting the sample file here. Thank you.Employee greater than 10(PLVAC).pbix (161.4 KB)

2

Hello @supergallagher25,

Thank You for posting your query onto the Forum.

Below is the screenshot provided for the reference of the formula as well as the result that you’re looking for.

Distinct Count of Employees

I’m also attaching the PBIX file for the reference.

Hoping you find this useful and meets your requirements. :slightly_smiling_face:

Thanks and Warm Regards,
Harsh

Employee greater than 10(PLVAC) - Harsh.pbix (162.0 KB)

2 Likes
3

Thank you Harsh, this worked really well. However, I also did another measure that stated less than or equal to 10 days and used the same formula as your with a little change,

SWITCH( TRUE() ,
ISBLANK( [OSS/EXP_] ) , BLANK() ,
[Days Between (OSS/EXP)] < 10 , 1 ,
0 )

However, my total for the table shows zero for that. Is there a workaround for that?

4

Hello @supergallagher25,

You’re Welcome. :slightly_smiling_face:

I’m glad I was able to help you.

Just want to check on the new measure that you’ve written. Are to trying to calculate the ID’s where leave is less than 10 days?

If yes, than below is the screenshot provided for the reference where I’ve written a measure firstly to find out how many EmpID’s have taken leave less than 10 days and than to correct/fix the totals I followed the “Measure Branching” technique and wrote another measure so that I can see how my calculations are done rather than doing it virtually. One of our expert member @Greg had put up a great post on “How to fix the totals”. I’m also providing a link of that post as well as the working of the PBIX file for the reference.

Less than 10 Days - Harsh = 
SWITCH( TRUE() , 
    ISBLANK( [Days Absent(PL/VAC)] ) , BLANK() , 
    [Days Absent(PL/VAC)] < 10 , 1 , 
0 )

Total No. of Employees with leave less than 10 days = 
SUMX(
    SUMMARIZE( t_Employee_Attendance , t_Employee_Attendance[EmpID] , "Total EmpID" , [Less than 10 Days - Harsh] ) ,
[Total EmpID] )

Distinct Count of Employees - 2
Distinct Count of Employees - 21011×345 40 KB

Thanks and Warm Regards,
Harsh

Employee greater than 10(PLVAC).pbix (162.5 KB)

1 Like
5

Thank you

6

Hello @supergallagher25,

You’re Welcome. :slightly_smiling_face:

I’m glad that I was able to help you.

Thanks and Warm Regards,
Harsh