develop measure filtered by another measure using context transition

I am using Microsoft Power BI Desktop (.pbix) and DAX language.

I have the below scenario:

I have a table called EmployeeTable; it has 9 distinct employees, but each employee can have more than one row, based on the number of jobs she has had ; say, George has 2 jobs (Painter, Driver), and hence there are two rows for him.

The column: EmployeeTable[IsActiveEmployee] determines whether an employee is active ?

This column can have only "Yes" or "No" values, abbreviated by "Y" and "N" respectively.

Now, the main requirement:

Develop a measure to get the headcount of ACTIVE employees with multiple jobs.

1) First identify the employees who have the value "Y" for the column EmployeeTable[IsActiveEmployee].

2) Then, filter this list further, restricting it only to employees with more than one job.

3) Then, get a DISTINCTCOUNT of the employees.

There are 11 distinct employees; 7 of them are active at least on one job; among these 7 active folks , I need to get a headcount of folks with more than one job while active.

While George (EmployeeId = 3), Adam (EmployeeId = 4), Rebecca (EmployeeId = 6), Jeanne (EmployeeId = 9), James (EmployeeId = 10), Jack (EmployeeId = 11) have multiple jobs, Adam must be excluded, since he is NOT an active employee; James must also be excluded, since he has had only one job while as an active employee.

I need the answer as 4, i.e. four folks (George,Rebecca,Jeanne,Jack) have more than one job, while active.

How do I get this ?

I tried something like this:

HeadCountOfEmployees_Measure

=

DISTINCTCOUNT(EmployeeTable[EmployeeId])
 




HeadCountOfActiveEmployees_Measure

=

CALCULATE (

            [HeadCountOfEmployees_Measure],

            KEEPFILTERS(EmployeeTable[IsActiveEmployee] = "Y")

           )
 




DistinctCountOfJobs_Measure

=

DISTINCTCOUNT(EmployeeTable[Job])
 




HeadCountOfActiveEmployeesWithMultipleJobs_Measure

=

SUMX(

        FILTER(

                VALUES(EmployeeTable[IsActiveEmployee]),

                EmployeeTable[IsActiveEmployee] = "Y"

              ),

        IF(

            [DistinctCountOfJobs_Measure] > 1,

            1,

            0

          )

     )

Its' not working. Can someone provide some suggestion ?

I have detailed everything in this .pbix file and Excel file.

Active Multi Job1_Measure

=

VAR x_CT = SUMMARIZECOLUMNS(

                             EmployeeTable[EmployeeId],

                             TREATAS({"Y"},EmployeeTable[IsActiveEmployee]),

                             "DistinctCountOfJobs_Measure",DISTINCTCOUNT(EmployeeTable[Job])

                            )

RETURN

COUNTROWS(

           FILTER(

                   x_CT,

                   [DistinctCountOfJobs_Measure] > 1

                 )

          )