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
)
)