COUNTROWS vs COUNT vs COUNTA

As a data modeler, sometimes you might need to write a DAX expression that counts table rows. The table could be a model table or an expression that returns a table.

Your requirement can be achieved in two ways. You can use the COUNT function to count column values, or you can use the COUNTROWS function to count table rows. Both functions will achieve the same result, providing that the counted column contains no BLANKs.

The COUNT function counts the number of records that contain a number in a single-column table.

The COUNTA function counts the number of records that aren’t blank in a single-column table. This function includes empty text (“”) in the count.

The following measure definition presents an example. It calculates the number of OrderDate column values.DAXCopy

Sales Orders =
COUNT(Sales[OrderDate])

Providing that the granularity of the Sales table is one row per sales order, and the OrderDate column does not contain BLANKs, then the measure will return a correct result.

However, the following measure definition is a better solution.DAXCopy

Sales Orders =
COUNTROWS(Sales)

There are three reasons why the second measure definition is better:

  • It’s more efficient, and so it will perform better.
  • It doesn’t consider BLANKs contained in any column of the table.
  • The intention of formula is clearer, to the point of being self-describing.