Calculated columns › SWITCH9 / 50

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

EasyFreeSWITCH

SWITCH: position allowance

Payroll pays a monthly position allowance: Manajer (manager) Rp3,000,000, Supervisor Rp1,500,000, and any other position Rp500,000. Dimas wants this value available as a column in the employee table.

Task

Create the calculated column Allowance in the Employees table using SWITCH on Employees[Position]:

"Manajer" → 3000000
"Supervisor" → 1500000
anything else → 500000

Calculated columnEmployees[Allowance]

Required functionsSWITCH

Expected result

Loading the expected result…

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

Hint 1
SWITCH is great for mapping the values of one column to several possible results, and it is tidier than nested IFs.
Hint 2
The pattern is SWITCH ( expression, value1, result1, value2, result2, else_result ). The last unpaired argument is the default.
Hint 3
SWITCH ( Employees[Position], "Manajer", 3000000, ___ )

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

Employees[Allowance]

Employees[Allowance] = SWITCH ( Employees[Position], "Manajer", 3000000, "Supervisor", 1500000, 500000 )

Explanation

SWITCH evaluates Employees[Position] once per row and compares it with "Manajer" and "Supervisor" in order. Rows that do not match (Staf, staff) get the default value 500000. The result is 6 managers, 8 supervisors and 26 staff.

Write numbers without thousands separators: in DAX the dot is the decimal mark.
If you forget the default, Staf rows become BLANK and the allowance total in the report silently comes out too small.

Data model: hr· table Employees

Employees[Allowance] =

Loading the DAX editor…

Write the DAX expression, then press Check answer.

Validation result