DAX Function Implementation: HighlightResult

This documentation outlines a DAX function to highlight results based on specific employee criteria. The function checks the employment start date and the entitlement status to determine if a particular employee should be highlighted in reports.

Function Overview

The HighlightResult function identifies new employees who started between August 2024 and the current date, and also checks if the relevant entitlement data is absent. It returns a value of 1 for highlighting and 0 otherwise.

DAX Code

HighlightResult = 
VAR StartDate = MIN('Master Stafflist'[StartedDate]) // Get the earliest start date.
VAR AugustStart = DATE(2024, 8, 1) // Define the cutoff start date for new employees.
VAR CurrentDate = TODAY() // Get today's date.
VAR HighlightNewEmployee = IF(
    StartDate >= AugustStart && StartDate <= CurrentDate, // Check if the start date is within range.
    1, // Return 1 if the condition is satisfied.
    0 // Otherwise, return 0.
)
VAR EntitledDays = COALESCE(SUM('Master Entitlement'[EntitledDays]), BLANK()) // Sum of entitled days or BLANK if none.
VAR DaysAvailable = COALESCE(SUM('Master Entitlement'[DaysAvailable]), BLANK()) // Sum of available days or BLANK if none.
VAR DaysUsed = COALESCE(SUM('Master Entitlement'[Days Used]), BLANK()) // Sum of used days or BLANK if none.
VAR HighlightSpace = IF(
    ISBLANK(EntitledDays) && ISBLANK(DaysAvailable) && ISBLANK(DaysUsed), // Check if all entitlement-related fields are blank.
    " ",  // Return blank if true.
    0 // Return 0 if false (not needed since it will be ignored).
)

RETURN 
    IF(
        HighlightNewEmployee = 1 || HighlightSpace = 1, // Determine if new employee or blank space.
        1, // Return 1 to indicate highlighting required.
        0 // Return 0 otherwise.
    )

Explanation of Variables

1. StartDate: This variable captures the minimum start date of employees to analyze against predetermined criteria.
2. AugustStart: A constant date set to August 1, 2024, which serves as a threshold for new employee identification.
3. CurrentDate: Fetches the current date, ensuring the function is always relative to the present day.
4. HighlightNewEmployee: Evaluates whether the employee's start date falls within the defined window.
5. EntitledDays, DaysAvailable, DaysUsed: These compute total sums for respective entitlement measures, returning blank if no data is available.
6. HighlightSpace: This checks if all entitlement-related fields are blank, signaling a need for possible formatting.

Usage Example

To use the HighlightResult function in a Power BI report:

• Add the calculated column directly to a relevant table, such as 'Master Stafflist'.
• Ensure your data model includes the necessary relationships to accurately calculate entitlement values.
• Use this calculated column in conditional formatting to visually highlight new employees within visualizations or tables.

Conclusion

The HighlightResult function provides a useful tool for assessing employee data for reporting. By implementing effective validations and calculations, it highlights key conditions that can help HR and management teams in decision-making processes. For advanced training and resources on DAX and data modeling, consider exploring the Enterprise DNA Platform.