Calculate active headcount and filtered with probation period

[nailed]

Hi, master

Could you help me how to count an active headcount with filtered probation period?

I have a table with employee details and a table with employee status month by month also a date table

Here is the current measure :

Headcount =

CALCULATE(COUNTX(FILTER('Employees','Employees'[Join Date]<=MAX('dDates'[Date]) && ISBLANK('Employees'[Resign Date]) || 'Employees'[Resign Date]>MAX('dDates'[Date])),'Employees'[Emp ID]))+0

Emp_Id	Month	Status
10028	01/01/2021	Probation
10028	01/02/2021	Permanent
10028	01/03/2021	Permanent
10028	01/04/2021

[v-jingzhang · Accepted solution]

Hi @nailed 

You can try a measure like below to count distinct employee IDs from Employee Status table and put Status column into a slicer or filter pane to filter by.

Headcount =
CALCULATE (
    DISTINCTCOUNT ( 'employee status'[Emp ID] ),
    FILTER (
        'Employees',
        'Employees'[Join Date] <= MAX ( 'dDates'[Date] )
            && (
                ISBLANK ( 'Employees'[Resign Date] )
                    || 'Employees'[Resign Date] > MAX ( 'dDates'[Date] )
            )
    )
) + 0

Or if you want to filter it directly in the measure, you can try 

Headcount =
CALCULATE (
    DISTINCTCOUNT ( 'employee status'[Emp ID] ),
    FILTER (
        'Employees',
        'Employees'[Join Date] <= MAX ( 'dDates'[Date] )
            && (
                ISBLANK ( 'Employees'[Resign Date] )
                    || 'Employees'[Resign Date] > MAX ( 'dDates'[Date] )
            )
    ),
    'employee status'[Status] = "Probation"
) + 0

Note that the Employees table should be able to filter 'Employee Status' table by connecting them on Emp ID columns. 

If this doesn't work, please share some sample data and expected result that we can use to test on and check the result.

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

[visheshjain]

Hi @nailed ,

Please correct me if I have understood the problem wrong.

Create a relationship using the emp_id column between employee details and employee tables and then write a measure using the COUNTROWS() function on your fact table, with the filters of 'Active' from the Dim table (employee details table) and Probation status from the fact table(employee table).

I am unable to understand why do you have date filters in your measure. You can simply create a realtionship between your calendar/date table and your fact table and then put the date/year month as a slicer or as a column in your table/matrix visual.

Hope this helps.

P.S. - Performance wise COUNTROWS() is better than COUNT().

Thank you,

Vishesh Jain