Counting Rows in a Table Using COUNTROWS in DAX

What Is COUNTROWS in DAX?

COUNTROWS counts the number of rows in a table or table expression.
Unlike functions such as COUNT, which count values in a column,
COUNTROWS works directly with a table.

The basic syntax is:

COUNTROWS(<Table>)

The <Table> argument can be an existing table or any DAX
expression that returns a table.

1. Retrieving the Complete Table

Query

EVALUATE
    CountTable

Step-by-step explanation

Step 1: EVALUATE
EVALUATE tells DAX to return the result of a table expression.

Step 2: CountTable
CountTable is the table that we want to inspect.
Since it is used directly after EVALUATE, DAX returns all the
rows and columns available in that table.

What will you get?

The query returns the complete CountTable. The actual number of
rows and columns depends on the data available in your model.

The example above is only for illustration. Your actual result will contain
the columns and rows available in your CountTable.

2. Counting the Rows Using COUNTROWS

Query

EVALUATE
    ROW(
        "Rows", COUNTROWS(CountTable)
    )

Step-by-step explanation

Step 1: EVALUATE
EVALUATE returns the result of the table expression.
Since ROW returns a table containing one row, this query produces
a valid DAX query result.

Step 2: ROW
ROW creates a single-row table. Here, we use it to display the
result of our row-counting calculation.

Step 3: “Rows”
“Rows” is the name assigned to the output column.
This makes the result easier to understand.

Step 4: COUNTROWS(CountTable)
COUNTROWS receives CountTable as its table argument
and counts the rows contained in that table.

What will you get?

The result is a one-row, one-column table containing the number of rows in
CountTable.

If your CountTable contains 3 rows, the result will be
3. If the table contains 10,000 rows, the result will be
10,000.

3. Understanding COUNTROWS with a Table Expression

One of the important features of COUNTROWS is that it does not
require only a physical table name. It can also count rows from a table expression.

For example, a filtered table can be passed to COUNTROWS.
This allows you to count only the rows that satisfy a particular condition.

COUNTROWS(
    FILTER(
        CountTable,
        <Condition>
    )
)

In this pattern, FILTER first produces a table containing only
the rows that meet the condition. Then COUNTROWS counts the rows
in that filtered table.

COUNTROWS vs COUNT

COUNTROWS and COUNT both involve counting, but
they operate at different levels.

When your requirement is specifically to determine the number of records in a
table, COUNTROWS expresses that intention directly.

Important Notes

1. COUNTROWS works with tables

The argument supplied to COUNTROWS should be a table or a table
expression that produces a table.

2. COUNTROWS returns a scalar value

Although COUNTROWS operates on a table, its result is a single
numeric value representing the number of rows.

3. ROW helps display the result in a DAX query

A DAX query using EVALUATE needs to return a table expression.
ROW can be used to turn the calculated row count into a
one-row result table.

4. COUNTROWS is useful with filtered tables

Because COUNTROWS can work with table expressions, it is commonly
used together with functions that create or filter tables.

EVALUATE
    ROW(
        "Total Rows", COUNTROWS(CountTable)
    )

📥 Practice Data:
Download Practice Data Free

🎥 Prefer learning through video?
DAX YouTube Playlist