DAX Function for Highlighting New Employees

Overview

This DAX function, HighlightResult, is designed to identify new employees who started between August 1, 2024, and the current date. It also handles cases where entitlement days data may be unavailable, providing a way to highlight the relevant records in your model.

Function Code

HighlightResult = 
VAR StartDate = MIN('Master Stafflist'[StartedDate]) 
VAR AugustStart = DATE(2024, 8, 1) 
VAR CurrentDate = TODAY() 
VAR HighlightNewEmployee = IF(
    StartDate >= AugustStart && StartDate <= CurrentDate, 
    1, 
    0 
) 

VAR EntitledDays = COALESCE(SUM('Master Entitlement'[EntitledDays]), BLANK()) 
VAR DaysAvailable = COALESCE(SUM('Master Entitlement'[DaysAvailable]), BLANK()) 
VAR DaysUsed = COALESCE(SUM('Master Entitlement'[Days Used]), BLANK()) 

VAR HighlightSpace = IF(
    ISBLANK(EntitledDays) && 
    ISBLANK(DaysAvailable) && 
    ISBLANK(DaysUsed), 
    " ",  
    0 
) 

RETURN 
    IF(
        HighlightNewEmployee = 1 || HighlightSpace = 1, 
        1, 
        0 
    )

Documentation

Purpose

The HighlightResult function computes a value based on conditions related to employee start dates and entitlement days.

Parameters

• It does not take explicit parameters as it's working on a table context.

Return Type

• Returns an integer, either 1 (to highlight) or 0 (no highlight), based on the defined conditions.

Exceptions

• The function will return blanks if the data references are missing or if the StartedDate field is empty.

Input Validation

• The function ensures data integrity by checking if the StartedDate and entitlement fields are not blank.
• Uses COALESCE to handle null or empty results, supporting smoother User Experience (UX).

Code Commentary

• VAR StartDate gets the earliest start date for any employee record.
• AugustStart defines a fixed date for comparison.
• CurrentDate retrieves today’s date for dynamic checking.
• The variable HighlightNewEmployee checks if a new employee's start date falls within the acceptable range.
• The HighlightSpace variable handles situations where entitlement data is missing by checking if all entitlement fields are blank.
• The final RETURN checks if either of the conditions (new employee or missing entitlement data) are met.

Usage Example

When using this DAX customized measure in your Power BI report:

1. Add the measure HighlightResult to a visual or a table.
2. Any employee record that falls within the defined date range will be marked with 1 for highlighting.
3. Records with missing entitlement data will also be marked with 1.

If you're looking to improve your DAX skills, consider exploring courses from the Enterprise DNA Platform for additional insights and hands-on practice.