DAX Function Modification and Enhancement

Overview

This document provides a modified version of the original DAX function HighlightResult. The new function is called HighlightResultWithSpace. It enhances the logic by integrating a replacement mechanism for any blanks with a space character. This ensures the function handles blank values more gracefully, maintaining functionality while introducing a clearer output.

Modified DAX Function

Function Definition

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

VAR HighlightNewEmployee = 
    IF(
        NOT ISBLANK(StartDate) && StartDate >= AugustStart && StartDate <= CurrentDate,
        1,
        0
    )

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

VAR AllEntitlementsBlank = 
    IF(
        EntitledDays = 0 && DaysAvailable = 0 && DaysUsed = 0,
        1, 
        0
    )

RETURN 
    IF(
        HighlightNewEmployee = 1 || AllEntitlementsBlank = 1, 
        " ",  // Replace 1 with space for better output handling
        0
    )

Key Enhancements and Structure

1. Input Variables:

   • StartDate: Captures the minimum start date.
   • AugustStart: Sets a fixed date for comparison.
   • CurrentDate: Retrieves today’s date for dynamic comparisons.

2. Logic for New Employees:

   • HighlightNewEmployee: Checks if the employee's start date is between August 1, 2024, and today.

3. Entitlement Calculation:

   • EntitledDays, DaysAvailable, DaysUsed: Use COALESCE to handle potential BLANK values, ensuring proper calculations.

4. Blank Check Logic:

   • AllEntitlementsBlank: Determines if all entitlement values are blank or zero.

5. Final Return Value:

   • Instead of returning 1, we return a space (" ") if any condition for highlighting a new employee or blank entitlement is met. Otherwise, we return 0.

Example Usage Scenario

To implement this function, follow these steps:

1. Create a New Measure:

   • Navigate to your Power BI report.
   • In the Fields pane, right-click on the desired table and select "New measure".
   • Copy and paste the modified function code into the formula bar.

2. Use the Measure in Your Reports:

   • Drag the HighlightResultWithSpace measure into a visual (e.g., a table or card) to see highlighted new employees or blank values replaced with space.

Conclusion

This DAX function, HighlightResultWithSpace, efficiently encapsulates the logic of employee highlighting while transforming blank responses into spaces for better visual clarity. This aligns with best practices in DAX coding and provides a scalable solution for reporting needs.

Feel free to explore further resources and courses on DAX through the Enterprise DNA Platform to enhance your skills in data modeling and analysis.