Salary Range and DAX Code Analysis

This code is written in DAX (Data Analysis Expressions), which is used in Power BI for data modeling and analysis.

Code Analysis

The provided DAX script calculates the percentage of active employees in each department who are in the high salary range. It follows a series of operations:

1. Counting the total number of active employees per department.
2. Counting the number of active employees per department who have a salary higher than designated salary range high.
3. Dividing the number of high salary earners by the total number of employees per department.

Documentation

% SalaryRangeHigh = 
    -- Declare variable to store the total number of active employees for each department
    VAR DepartmentTotalActiveEmployees =
        -- Count rows in _EmployeeDetail,
        -- but only count rows where the EmploymentStatus equals to "Active",
        -- and where the Salary column in the _EmployeeSalary table is not blank
        -- Furthermore, only count distinct values in all columns of _EmployeeDetail 
        -- except the Department column
        CALCULATE(
            COUNTROWS(_EmployeeDetail),
            _EmployeeDetail[EmploymentStatus] = "Active",
            NOT ISBLANK(_EmployeeSalary[ Salary ]),
            ALLEXCEPT(_EmployeeDetail, _EmployeeDetail[Department]) 
            )

    -- Declare a variable to store the number of active employees having higher than high salary range for each department
    VAR DepartmentActiveEmployeesHigherRange =
        -- Count rows in _EmployeeDetail,
        -- but only count rows where the EmploymentStatus equals to "Active",
        -- and where the Salary column in the _EmployeeSalary table is not blank,
        -- and where the Salary is equal or greater than the SalaryRangeHigh
        -- Furthermore, only count distinct values in all columns of _EmployeeDetail 
        -- except the Department column
        CALCULATE(
            COUNTROWS(_EmployeeDetail),
            _EmployeeDetail[EmploymentStatus] = "Active",
            NOT ISBLANK(_EmployeeSalary[ Salary ]),
            _EmployeeSalary[ Salary ] >= _EmployeeSalary[SalaryRangeHigh], 
            ALLEXCEPT(_EmployeeDetail, _EmployeeDetail[Department]) 
            )

    -- Returns the division of the number of active employees with higher range salary by the total number of active employees per department
    RETURN 
        -- If the denominator is 0, it will return 0 instead of throwing an error
        DIVIDE(DepartmentActiveEmployeesHigherRange, DepartmentTotalActiveEmployees, 0) 

Note

The ALLEXCEPT function in the CALCULATE function is used to clear all context except for the Department. This is so the function counts distinct department values rather than all row values.

Also, DIVIDE function has a built-in error handling. If the denominator is zero, it returns a designated alternative result (in this case, zero), rather than returning an error.