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

Center of Excellence

DOWNLOAD


Download our comprehensive DAX Formula Reference Guide as perfect companion as you learn how to use DAX formulas within Power BI.


Download DAX Formulas Reference Guide
Download DAX Formula Reference Guide
Membership