DAX Filter - ALLNOBLANKROW function



Description

Returns all rows but the blank row, or all distinct values of a column but the blank row from the parent table of a relationship and disregards any context filters that might exist.

Syntax

ALLNOBLANKROW (<table>|<column>) 

Parameters

Sr.No. Parameter & Description
1

table

The table over which all context filters are removed.

2

column

The column over which all context filters are removed.

ALLNOBLANKROW takes only one argument, either table or column.

Return Value

  • A table, when the argument is a table.
  • A column of values, when the argument is a column.

Remarks

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.

Example

= COUNTROWS (ALLNOBLANKROW (Salesperson)) 

This DAX formula returns 7, if the number of rows in the parent Salesperson table is 7. However, there are entries in the Sales table for an unaccounted salesperson (i.e. the salesperson is not present in the Salesperson table).

= COUNTROWS (ALL (Salesperson))

This DAX formula returns 8 though the number of rows in the parent Salesperson table is 7, as there are entries in the Sales table for an unaccounted salesperson (i.e. the salesperson is not present in the Salesperson table).

dax_functions_filter.htm
Advertisements