Enhanced DAX Function for Highlighting New Employees

Overview

The goal is to modify the existing DAX code to handle scenarios where blank values in the dataset may affect the logic. This version will ensure that employees with a valid StartedDate are properly identified and will also more gracefully handle cases with missing entitlement data.

Revised Code

HighlightResult = 
VAR StartDate = MIN('Master Stafflist'[StartedDate])
VAR AugustStart = DATE(2024, 8, 1)
VAR CurrentDate = TODAY()

// Check if StartDate is valid and falls within the range
VAR HighlightNewEmployee = 
    IF(
        NOT ISBLANK(StartDate) && StartDate >= AugustStart && StartDate <= CurrentDate,
        1,
        0
    )

// Summing up entitled days, ensuring to handle blank cases
VAR EntitledDays = COALESCE(SUM('Master Entitlement'[EntitledDays]), 0) // Default to 0 if blank
VAR DaysAvailable = COALESCE(SUM('Master Entitlement'[DaysAvailable]), 0) // Default to 0 if blank
VAR DaysUsed = COALESCE(SUM('Master Entitlement'[Days Used]), 0) // Default to 0 if blank

// Check if all entitlement values are blank (using 0 for empty cases)
VAR AllEntitlementsBlank = 
    IF(
        EntitledDays = 0 && DaysAvailable = 0 && DaysUsed = 0,
        1, // Flag indicating that all are blank
        0
    )

// Return 1 if the employee is new or if all entitlements are blank
RETURN 
    IF(
        HighlightNewEmployee = 1 || AllEntitlementsBlank = 1, 
        1, 
        0
    )

Key Changes and Improvements

1. Input Validation: The function now checks if StartDate is blank before performing comparisons. This prevents errors that could occur from evaluating an invalid date.

2. Robust Handling of Blank Values: COALESCE now defaults to 0 rather than BLANK(), allowing the logical checks for entitled days to remain valid when data is missing.

3. Simplified Conditional Logic: A separate variable (AllEntitlementsBlank) flags whether all entitlements are zero, making the final return condition clearer and easier to maintain.

Usage Example

This function can be utilized in a DAX measure to highlight new employees who started on or after August 1, 2024, or to identify cases where entitlement data is completely missing, resulting in a visual cue in reports or dashboards.

// Example Usage in a measure
NewEmployeeHighlight =
    CALCULATE(
        SUM('Master Stafflist'[SomeMetric]), // Example metric to sum
        FILTER(
            'Master Stafflist',
            [HighlightResult] = 1
        )
    )

Conclusion

The redesign of the HighlightResult function now accommodates blank values and ensures robust performance. With enhanced readability and maintainability, it serves as a strong basis for ongoing analytical work in your data model. For further professional development in DAX and data analysis techniques, consider courses available on the Enterprise DNA Platform.