Table functions › SELECTCOLUMNS, FILTER, RELATED38 / 50

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

MediumFreeSELECTCOLUMNSFILTERRELATED

SELECTCOLUMNS: contract employee list

Mrs. Lestari from the HR team needs a list of employees with status Kontrak (contract) for the contract renewal review. The list only needs name, department and salary, but the department name lives in another table (Departments).

Task

Create the calculated table Karyawan Kontrak (contract employees) with SELECTCOLUMNS over the Employees table filtered with FILTER to Employees[Status] = "Kontrak". The result has exactly 3 columns:

Name = Employees[Name]
Department = Departments[Department], fetched with RELATED
Salary = Employees[Salary]

Calculated tableKaryawan Kontrak

Required functionsSELECTCOLUMNSFILTERRELATED

Expected result

Loading the expected result…

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

Hint 1
Work from the inside out: first keep the Employees rows with status Kontrak using FILTER, then pick the columns with SELECTCOLUMNS.
Hint 2
The pattern is SELECTCOLUMNS ( table, "Column Name", expression, "Column Name 2", expression2, ... ). SELECTCOLUMNS has a row context, so RELATED can fetch a column from the Departments table.
Hint 3
SELECTCOLUMNS ( FILTER ( Employees, ___ ), "Name", Employees[Name], "Department", ___, "Salary", ___ )

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 Kontrak

Karyawan Kontrak = SELECTCOLUMNS (
    FILTER ( Employees, Employees[Status] = "Kontrak" ),
    "Name", Employees[Name],
    "Department", RELATED ( Departments[Department] ),
    "Salary", Employees[Salary]
)

Explanation

FILTER keeps the 5 contract employees, then SELECTCOLUMNS builds a new table with three columns named after the text in its arguments. Because SELECTCOLUMNS iterates the Employees rows, RELATED can follow the Employees[DeptKey] to Departments[DeptKey] relationship to fetch the department name, for example Indah Gunawan in Penjualan (sales).
The difference from ADDCOLUMNS: SELECTCOLUMNS keeps only the columns you name, while ADDCOLUMNS keeps every column of the source table and adds new ones. Column names are written as quoted text and must match the request exactly.

Data model: hr

Loading the DAX editor…

Write the DAX expression, then press Check answer.

Validation result