Power Pivot Principles: Introducing the ALLNOBLANKROW Function

Welcome back to the Power Pivot Principles blog.  This week, we will talk about the ALLNOBLANKROW in DAX.

The ALLNOBLANKROW returns all the rows except for the blank rows in a table, or all the values in a column, ignoring any filters that may have been applied.  This function has the following syntax:

in which:

• table: the table over which all context filters are removed
• column: a column over which all context filters are removed.

The return value, in particular, is a table, when the passed parameter was a table, or a column of values, when the passed parameter was a column.

The ALLNOBLANKROW function does not consider truly blank rows in a table, but only handles the blank row that is a special case generated in a parent table, when one or more of the child tables in the relationship contain non-matching values or blank values.  Clear as mud?

Let’s consider an example.  Imagine there is a Store_ID table which lists five store IDs and their respective names:

Also, there is a Car_Sales_Record table which records the sales of car by Date, Store ID and Sales Person:

The above two tables are already loaded into the Power Pivot Data Model and a relationship is set up between them, by connecting the Store ID fields in the two tables, viz.

Before we do anything, we may notice that there are only five store details recorded in the Store_ID table. Meanwhile, car sales are listed in six stores in the Car_Sales_Record table. There is one store missing information here. Therefore, when we create a PivotTable working with Store Name field, a (blank) cell will be displayed:

We will create a measure to count the table rows using the ALLNOBLANKROW function:

To compare, we will create another measure using the ALL function:

The results show a difference of one (1) row in the table rows count.

The DAX formula using the ALLNOBLANKROW function returns five (5).  This is because the number of rows in the parent Store_ID table is five (5), but there are entries in the Car_Sales_Record table for an unaccounted store, i.e. store number 6 is not present in the Store_ID table.  This is not counted, as the ALLNOBLANKROW function excludes blank rows when it is counting.

Meanwhile, the DAX formula using the ALL function return six (6).  This is because the number of rows in the parent Store_ID table is five (5), but again, there are entries in the Car_Sales_Record table for store number 6 that are not present in the Store_ID table.

One thing worth noting is that the ALLNOBLANKROW function does not count any truly blank rows: it only handles the special blank row on the parent table.  We will create two other measures to count table rows.  Instead of counting Store Name from the Store_ID table, we will count Store ID from the Car_Sales_Record table:

Now the two measures return the same results, which is because the ALLNOBLANKROW function does not count truly blank rows in a table, but only handles the blank row that is a special case generated in a parent table, when one or more of the child tables in the relationship contain non-matching values or blank values.

To extend this example, we can revisit the VALUES and DISTINCT functions:

The VALUES function counts and displays blank values, whereas the DISTINCT function does not. You can read more about the comparison between the VALUES and DISTINCT functions here.

That’s it for this week!


Stay tuned for our next post on Power Pivot in the Blog section. In the meantime, please remember we have training in Power Pivot which you can find out more about here. If you wish to catch up on past articles in the meantime, you can find all of our Past Power Pivot blogs here.