Daily DAX : Day 297 ALLNOBLANKROW

ALLNOBLANKROW DAX Function

Purpose

Removes filters from a table or column in Power BI DAX, excluding blank rows.

Syntax

ALLNOBLANKROW( <Table> | <Column> [, <Column> ...] )

Key Points

• Removes all filters from specified table/column(s).
• Excludes blank rows, unlike ALL.
• Returns all rows or unique values, ignoring filters.

Use Case

Calculate percentages (e.g., sales by category) while ignoring blank rows in data.

Example

Data:

ProductID	SalesAmount
1	100
2	200
(blank)	50

DAX Measure:

Percentage of Total Sales =
DIVIDE(
    SUM(Sales[SalesAmount]),
    CALCULATE(
        SUM(Sales[SalesAmount]),
        ALLNOBLANKROW(Sales)
    ),
    0
)
    

Result:

• Electronics: 33.33% (100 / 300)
• Clothing: 66.67% (200 / 300)
• Blank row ($50) excluded.

When to Use

• Percentage calculations (e.g., market share).
• Data models with unmatched rows.
• Filter context manipulation without blank rows.

Notes

• Use ALL if blank rows are needed.
• Can be resource-intensive on large datasets.