DAX Function for Highlight and Aggregate

This document presents a DAX function designed to highlight specified columns in a dataset and aggregate numeric values. It incorporates checks for blank values and returns modified results according to the specified requirements.

Function Overview

Purpose

To highlight certain columns in a data table and to sum numeric columns while handling blank values accurately, facilitating data quality and clarity.

Components of the Function

1. HighlightSpace Calculation: Determines if all entitlement values are blank.
2. HighlightBlanksAndSum: Modifies the table to replace blanks with a space, aggregating numeric values as required.

DAX Code Implementation

1. HighlightSpace Calculation

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

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

RETURN 
    AllEntitlementsBlank

2. HighlightBlanksAndSum Calculation

HighlightBlanksAndSum = 
VAR BlankReplacedTable =
    ADDCOLUMNS(
        Table,
        "HighlightedColumn", 
        IF(ISBLANK(SELECTEDVALUE(Table[ColumnToHighlight])), " ", SELECTEDVALUE(Table[ColumnToHighlight]))
    )
VAR AggregatedValues =
    SUMMARIZE(
        BlankReplacedTable,
        BlankReplacedTable[HighlightedColumn],
        "SummedValues", 
        SUMX(
            BlankReplacedTable,
            IF(ISBLANK(SELECTEDVALUE(Table[NumericColumn1])), 0, Table[NumericColumn1]) +
            IF(ISBLANK(SELECTEDVALUE(Table[NumericColumn2])), 0, Table[NumericColumn2])
            // Add more numeric columns as needed
        )
    )
RETURN
    AggregatedValues

Explanation of Code

HighlightSpace Function

• Variables Declaration: Uses IF(ISBLANK(...), " ", SUM(...)) to check for blank values and return a space or the summed value.
• Condition Check: If all key values (EntitledDays, DaysAvailable, DaysUsed) are blank, it sets AllEntitlementsBlank to 1; otherwise 0.
• Return Value: Outputs the value of AllEntitlementsBlank indicating if all entitlements are blank.

HighlightBlanksAndSum Function

• ADDCOLUMNS: Creates a new column in the Table to replace blank items in ColumnToHighlight with a space.
• SUMMARIZE: Groups the modified table by HighlightedColumn and calculates the sum of numeric values, handling blank values gracefully.
• Aggregation: Uses SUMX to compute summed values while ignoring blanks by substituting them with 0.

Usage Example

Assuming the following structure:

• Table: Contains columns for highlighting and numeric values such as ColumnToHighlight, NumericColumn1, and NumericColumn2.
• You can use HighlightBlanksAndSum in a report to get highlighted entries and their corresponding numeric totals:

EVALUATE 
SUMMARIZE(
    HIGHLIGHTBLANKSANDSUM,
    [HighlightedColumn],
    [SummedValues]
)

Learning Resources

For a deeper understanding and more advanced techniques, consider exploring courses on the Enterprise DNA Platform that focus on DAX and data modeling.

Conclusion

The above DAX functions provide a robust solution for highlighting columns in a dataset and summing numeric values while handling blanks effectively. By using these functions, you can enhance data clarity and simplify analysis in your reports.