COUNTROWS DAX Function (Aggregation)
Counts the number of rows in a table.
The table containing the rows to be counted. If it is not specified, it uses the table containing the measure definition.
Number of rows obtained by the evaluation of the table expression. If the table has no rows, it returns blank.
This function can be used to count the rows of a table expression.
Even though the table argument is optional, it is a best practice to always specify the first argument to improve readability and simplify code refactoring when needed.
» 2 related functions
The following are valid syntaxes.
COUNTROWS ( table ) COUNTROWS ( DISTINCT ( table ) ) COUNTROWS ( VALUES ( table ) )
The COUNTROWS function can be used to count the unique values available in a column for the current filter context. However, DISTINCTCOUNT is better in that case. The following expressions are equivalent.
COUNTROWS ( DISTINCT ( table[column] ) ) DISTINCTCOUNT ( table[column] ) )
The COUNTROWS function can be used to check whether a column has only one item filtered/selected in the current filter context. However, HASONEVALUE is better in that case. The following expressions are equivalent.
COUNTROWS ( VALUES ( table[column] ) ) = 1 HASONEVALUE ( table[column] ) )
-- COUNTROWS counts the number of rows in a table DEFINE MEASURE Customer[# Customers] = COUNTROWS ( Customer ) MEASURE Customer[# Countries 1] = COUNTROWS ( DISTINCT ( Customer[CountryRegion] ) ) MEASURE Customer[# Countries 2] = DISTINCTCOUNT ( Customer[CountryRegion] ) EVALUATE SUMMARIZECOLUMNS ( Customer[Continent], "# Customers", [# Customers], "# Countries 1", [# Countries 1], "# Countries 2", [# Countries 2] )
|Continent||# Customers||# Countries 1||# Countries 2|
-- COUNTROWS is often used inside CALCULATE to count -- the number of rows in a filtered table DEFINE MEASURE Customer[# Individuals] = CALCULATE ( COUNTROWS ( Customer ), Customer[Customer Type] = "Person" ) MEASURE Customer[# Companies] = CALCULATE ( COUNTROWS ( Customer ), Customer[Customer Type] = "Company" ) EVALUATE SUMMARIZECOLUMNS ( Customer[Continent], "# Individuals", [# Individuals], "# Companies", [# Companies] )
|Continent||# Individuals||# Companies|
Learn more about COUNTROWS in the following articles:
- Handling BLANK in DAX
Blank row in DAX
There are two functions in DAX that return the list of values of a column: VALUES and DISTINCT. This article describes the difference between the two, explaining the details of the blank row added to tables for invalid relationships. » Read more
Other related functions are:
Last update: Jun 10, 2023 » Contribute » Show contributors
Contributors: Alberto Ferrari, Marco Russo
Microsoft documentation: https://docs.microsoft.com/en-us/dax/countrows-function-dax