Iterators (SUMX and more) › COUNTX, FILTER16 / 50

Progress is saved on this device. Sign in or sign up free to keep it in your account.

MediumFreeCOUNTXFILTER

COUNTX + FILTER: employees with heavy overtime

Mr. Hendra wants to track how many employees in each department work 10 or more overtime hours in a month, to prevent burnout. The measure [Total Overtime] (sum of overtime hours) already exists.

Task

Create the measure Karyawan Lembur Tinggi (heavy-overtime employees) in the Attendance table: FILTER the Employees table to employees with [Total Overtime] >= 10, then count the result with COUNTX over the Employees[EmployeeID] column.
Visual: department (Departments[Department]) × month (Calendar[MonthName]).

MeasureKaryawan Lembur Tinggiin table Attendance

Required functionsCOUNTXFILTER

Expected result

Loading the expected result…

Check answerCtrlShiftEnter
Run previewCtrlEnter
Previous / next exerciseAlt←→
Leave the workspaceEscTab

Hint 1
FILTER ( table, condition ) returns the table rows that meet the condition. A measure inside the condition is evaluated per employee row.
Hint 2
COUNTX ( table, expression ) counts the rows where the expression is not blank. The table is the FILTER result, the expression is the employee ID.
Hint 3
COUNTX ( FILTER ( Employees, ___ >= 10 ), Employees[EmployeeID] )

Reveal them one at a time. The last hint nearly gives the answer away.

Give it a try first. The solution unlocks automatically after your first check.

Answer

Karyawan Lembur Tinggi

Karyawan Lembur Tinggi = COUNTX ( FILTER ( Employees, [Total Overtime] >= 10 ), Employees[EmployeeID] )

Explanation

FILTER iterates the employees visible in the cell (the department from the visual row). For each employee, [Total Overtime] is evaluated through context transition together with the month filter from the visual column, so it compares that employee's overtime in that month. COUNTX then counts the remaining employees: 8 in January, 9 in February, 8 in March.
Look at the Total column: 38 people, far above the monthly numbers, because in the total the 10-hour threshold is compared with a whole quarter of overtime. That is what measures do: the logic is re-evaluated at every level. A shorter alternative with the same result is COUNTROWS ( FILTER ( ... ) ).

Data model: hr· table Attendance

Karyawan Lembur Tinggi =

Loading the DAX editor…

Write the DAX expression, then press Check answer.

Validation result