DAX Function to Highlight New Employees

Overview

This DAX function, HighlightResult, identifies new employees who started between a specified date range and checks if their entitlement information is blank. It utilizes variables to streamline calculations and enhance readability.

Function Code

HighlightResult = 
VAR StartDate = MIN('Master Stafflist'[StartedDate])  // Get the earliest start date of the employee
VAR AugustStart = DATE(2024, 8, 1)                     // Define the start date for the comparison
VAR CurrentDate = TODAY()                               // Get today's date

VAR HighlightNewEmployee = 
    IF(
        NOT ISBLANK(StartDate) && StartDate >= AugustStart && StartDate <= CurrentDate,
        1,  // Mark as new employee if they started within the date range
        0
    )

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

VAR AllEntitlementsBlank = 
    IF(
        EntitledDays = " " && DaysAvailable = " " && DaysUsed = " ",
        1,  // Check if all entitlement fields are blank
        0
    )

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

Explanation of Code Components

• Variable Definitions:

  • StartDate: Fetches the minimum start date from the 'Master Stafflist'.
  • AugustStart: Represents the threshold start date (August 1, 2024).
  • CurrentDate: Captures today's date to compare with StartDate.

• Logic for Highlighting New Employees:

  • HighlightNewEmployee: Checks if StartDate is not blank and falls within the designated range to flag it as a new employee.

• Entitlement Checks:

  • Computes total entitled days, days available, and days used, replacing null with a space.
  • AllEntitlementsBlank: Validates if all entitlement-related fields are blank.

• Return Statement:

  • Combines conditions to return 1 if either a new employee is flagged or all entitlement fields are blank.

Usage Example

This function can be included in a calculated column or measure in Power BI. Here’s how to implement it:

1. Open your Power BI Desktop.
2. Navigate to the Modeling tab and click on New Measure.
3. Paste the above DAX function into the formula bar.
4. Name the measure Highlight Result and hit Enter.

You can then use this measure in your reports to visually highlight new employees or those lacking entitlement data.

Conclusion

The provided DAX function HighlightResult is a powerful tool to dynamically assess employee status based on their hiring date and entitlement information. By utilizing robust validation checks and simplifying logic operations with variables, the function adheres to best coding practices.

For further development of your DAX skills, consider courses available on Enterprise DNA's platform. Explore and enhance your capabilities in a structured learning environment.