ALLNOBLANKROW Function (DAX)

How does the ALLNOBLANKROW function (DAX) work?

The ALLNOBLANKROW function (DAX) returns all rows in a table or all values in a column except for the blank row, while ignoring any context filters that might have been applied in the parent table of a relationship. 

ALLNOBLANKROW Formula Syntax

ALLNOBLANKROW(
     {<table> | <column> [, <column> [, <column>[,…]]]}
)

How do you use the ALLNOBLANKROW function?

The ALLNOBLANKROW has only one parameter which can either be a table or a column in which all context filters have been removed.  

Related Blog Posts

Related Support Forum Posts

EFFICIENTLY creating a Summarised “byday” Table
Filter out customers who don’t buy certain products
Cumulative Banking Transactions – ALL vs ALLSELECTED within cumulative totals

Considerations when using the ALLNOBLANKROW function

This function removes the filters from the filter context. It does not materialize the resulting table when called directly in a filter argument of CALCULATE or CALCULATETABLE. 

The ALLNOBLANKROW function only filters the blank row if there are one or more rows in the child table that has non-matching values to the parent column in the relationship. 

Related Video Tutorials

Formula examples using the ALLNOBLANKROW function

= COUNTROWS(
ALLNOBLANKROW(‘DateTime’)
)

=COUNTROWS(
ALLNOBLANKROW(‘ResellerSales_USD’)
)

ALLNOBLANKROW (
Customer[Country], Customer[State] , Customer[City]
)

Related DAX Functions

ALL
FILTER Function (DAX)

Related Course Modules

DOWNLOAD

To access our comprehensive DAX formula reference guide just insert your email address below. The resource will download immediately and we’ll also email it to you.