DAX Function to Highlight Blank Rows and Sum Data

In this document, we will create a DAX function that highlights blank rows as "space" and aggregates all columns that contain blank values using SUM. This function is useful for data presentation and reporting purposes, ensuring that blanks are visible while simultaneously providing a total for numeric fields where blanks occur.

Function Overview

Purpose

The function checks for blanks in a specified column of a table and replaces those blanks with a space. For numeric columns, it provides a SUM aggregation that accounts for rows containing blanks.

Parameters

• Table: The table you want to analyze.
• ColumnToHighlight: The column in which to check for blanks.
• NumericColumns: One or more numeric columns for which to calculate the SUM.

Return Type

• Returns a modified table with blank rows highlighted as " " (space) and numeric columns aggregated.

Exceptions

• Throws an error if the specified columns do not exist within the given table.

DAX Code

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

Code Explanation

1. ADDCOLUMNS: Creates a new calculated column, HighlightedColumn, which checks if the specified column contains a blank value. If it does, " " (space) is used; otherwise, the original value is retained.

2. SUMMARIZE: Groups the modified table by the HighlightedColumn and calculates the SummedValues using the SUMX function. This function sums the values of specified numeric columns, treating blanks as zeros.

3. IF Statements: Ensure that if a numeric column contains a blank, it counts as zero in the aggregation to prevent incorrect totals.

Usage Example

In a Power BI report, you can utilize this function as follows:

1. Assume you have a table named SalesData with columns: Product, SalesAmount, DiscountAmount, and you want to highlight blanks in the Product column and sum the SalesAmount and DiscountAmount.

2. You would call the function using:

SalesSummary = HighlightBlanksAndSum(SalesData, SalesData[Product], SalesData[SalesAmount], SalesData[DiscountAmount])

This will create a new table, SalesSummary, that replaces any blanks in the Product column with a space and sums the SalesAmount and DiscountAmount.

Conclusion

This DAX function is efficient and scalable for handling blank rows and summing relevant numeric data. For further learning on DAX and more advanced techniques, consider exploring the courses available on the Enterprise DNA Platform.