Downloads
DAXFilter and contextAdvanced

ALLNOBLANKROW

Returns all the rows of a table except the blank row that a broken relationship can add.

Syntax
ALLNOBLANKROW(<table>)

Returns: A table. Inside CALCULATE, it removes the filters on the table but keeps out the blank row.

What it does

When a fact table holds a key that matches no row in a dimension (a sale with a customer key missing from the Customer table), the model can show an extra blank row on the dimension side, so totals still add up. ALLNOBLANKROW returns the dimension without that extra row. Use it when you want to count real members only.

Syntax and arguments

Example

A finance team counts the customers on file, leaving out the placeholder row for unmatched sales.

Customers On File =
COUNTROWS ( ALLNOBLANKROW ( Customer ) )

Each real customer counts once. If some sales point to a customer key that does not exist in the Customer table, that unknown customer does not add to the count. The figure matches the customer master, which is what a reconciliation needs.

Things to watch

In business terms

Reconciliation is the main use. A count of customers or vendors on file must match the master file, and sales or bills that point to unknown records are a control issue. The blank row makes those gaps visible, and this function lets you count clean members while the gap is still reported elsewhere.

Related functions

All DAX functions · View as Markdown · DAX fundamentals