Percent of Employees Meeting Requirement by Scheduling Period

[jcecil2]

Rule: All Employees must work at least four weekend shifts in a six-week scheduling period. 

Date:

'All Shifts' - Contains all shifts by employee and date

'Dates' - Contains all dates and what scheduling period they occur in

'Employees' - Contains all Employees

Data Model:

I have created this simple DAX measure that calculates the total number of weekend shifts in the 'All Shifts' table:

# of Weekend Shifts = CALCULATE(COUNTROWS('All Shifts'), 'All Shifts'[is weekend?] = 1)

I need to calculate the percentage of employees who are meeting their requirement by scheduling period. I have come up with a pseudo-code algorithm that I believe would accomplish this, but I am having trouble translating this to DAX.

Pseudo-Code Algorithm:

For each scheduling period:

    EmpsMeetingReq = 0

    TotalEmps = DISTINCTCOUNT(Employees with Shifts in this Period)

    For each employee:

        If [# of Weekend Shifts] >= 4:

            Then: EmpsMeetingReqs += 1
            Else: EmpsMeetingReqs += 0

    % Meeting Req = EmpsMeetingReq/TotalEmps

Any help with this would be greatly appreciated. 

Thank you,

-Jacob

UPDATE:  I have another DAX measure that calculates the number of weekend shifts per employee. 

Weekend Shifts by Employee = SUMX(VALUES(Employees[Employee ID]), [# of Weekend Shifts])

I have tried creating the following as both a measure and a calculated column for the number of employees meeting their requirement by scheduling period, but I am not sure this is correct.

Emps Meeting Reqs by Period = CALCULATE(DISTINCTCOUNT(Employees[Employee ID]), FILTER(VALUES(Dates[Schedule Period]), [Weekend Shifts by Employee]>=4))

[jcecil2 · Accepted solution]

I was able to accomplish this by creating a DAX table using the following:

Weekend Shift Count by Emp and Period =

SUMMARIZECOLUMNS(

'Employees'[Employee ID],

Dates[Schedule Period],

Employees[Is Resource Center?],

'Types of Hours'[Role Category],

Dates[Schedule Period Start Date],

"COUNT",

COUNTX(FILTER('All Shifts', 'All Shifts'[is weekend?] = 1), 1)

)

Which can be plugged directly into EmpsMeetingReqs to find the number of employees meeting the requirement as follows:

Emps Meeting Reqs by Period = CALCULATE(DISTINCTCOUNT(Employees[Employee ID]), FILTER(

SUMMARIZECOLUMNS(

'Employees'[Employee ID],

Dates[Schedule Period],

Employees[Is Resource Center?],

'Types of Hours'[Role Category],

Dates[Schedule Period Start Date],

"COUNT",

COUNTX(FILTER('All Shifts', 'All Shifts'[is weekend?] = 1), 1)

)

, [COUNT]>=4))

[v-eachen-msft]

Hi @jcecil2 ,

You could use MAXX() function and use [# of Weekend Shifts] >= 4 as the filter to calculate EmpsMeetingReqs.

Then use DISTINCTCOUNT() and use  [EmpsMeetingReqs]=0 as the filter to calculate TotalEmps.

[jcecil2]

Hi @v-eachen-msft, 

Thank you for your response. Although, I am a little confused as to why I would use [EmpsMeetingReqs] = 0 as the filter for the DISTINCTCOUNT(). I would think that I am taking a DISTINCTCOUNT() of the Employee ID rather than the count of employees meeting the requirement.