DAX Measure to Count Rows: COUNTROWS Explained

To count rows in a DAX measure, use Row Count = COUNTROWS('Sales'), replacing 'Sales' with the table you want to count. The measure recalculates in the current filter context, so report filters, slicers, and visual groupings can change the result.

The basic COUNTROWS measure

COUNTROWS is the standard DAX function for counting rows in a table or table expression. A simple measure might be named Row Count and defined as Row Count = COUNTROWS('Sales'). The table name must match the name in your data model; single quotes around it are the conventional syntax and are required when the name contains spaces or certain special characters.

Create the expression as a measure in Power BI or another tabular model, rather than typing it into a worksheet cell. In Power BI Desktop, you can select a table in the Fields or Data pane and choose New measure, then enter the expression in the formula bar. The measure belongs to a model table for organization, but that does not limit it to counting only that table in every expression.

For example, if the Sales table has one row per order line, this measure counts order lines, not necessarily orders. The result is the number of records at the table’s stored granularity. Knowing what one row represents is essential before interpreting the result as a count of orders, customers, or another business entity.

How filter context changes the result

A DAX measure is evaluated when a visual requests a value. The model supplies the context for that calculation: filters from slicers, page or report filters, visual-level filters, and the row or column currently represented in a chart or matrix can all affect which Sales rows are visible to COUNTROWS.

Suppose a report has a row for each year. The measure Row Count = COUNTROWS('Sales') returns the number of Sales rows for each year, assuming the Date table is related appropriately to Sales. A card with no year filter can show the count across all years. Selecting a product or region usually narrows the rows counted if the relevant model relationships allow that filter to reach Sales.

This behavior is a major reason to use a measure rather than a fixed number. The measure responds to the report’s context each time it is evaluated. If a slicer appears to have no effect, check whether the slicer’s table is related to the counted table, whether the relationship is active, and whether its filter direction supports the intended filtering.

Count rows that meet a condition

To count only rows matching a condition, use CALCULATE to evaluate the row count under an added filter. With a table named Sales and a text column named Status, a measure for open rows is Open Rows = CALCULATE(COUNTROWS('Sales'), 'Sales'[Status] = "Open"). It counts Sales rows whose Status is Open while still responding to other applicable filters, such as date or region.

There is a filter-context detail worth knowing: a filter argument in CALCULATE generally replaces an existing filter on the same column. If a report user has selected Closed in a Status slicer, the Open Rows measure can still return the Open count because its Status condition replaces that column’s existing filter. To intersect the measure’s condition with the user’s selection instead, use Open Rows (Respect Selection) = CALCULATE(COUNTROWS('Sales'), KEEPFILTERS('Sales'[Status] = "Open")). With this version, a selection that excludes Open produces no matching rows.

For conditions that need more involved row-by-row logic, use FILTER to build a table of qualifying records. For example, to count sales rows with an amount greater than 1000, write High-Value Rows = COUNTROWS(FILTER('Sales', 'Sales'[Amount] > 1000)). Use a straightforward CALCULATE filter when possible; reserve FILTER for conditions that need its table-expression behavior or cannot be expressed as a simple filter argument.

What to do when the result is blank

COUNTROWS returns BLANK when the table expression contains no rows. That can make a card or chart look empty rather than displaying zero. Whether that is a problem depends on the report: a blank can communicate that no records match, while zero can make comparisons and totals easier to read.

If you specifically want a visible zero, wrap the count in COALESCE: Row Count (Zero) = COALESCE(COUNTROWS('Sales'), 0). This returns the count when rows exist and zero when the count would otherwise be blank. Check the visual’s display settings as well; the measure’s result and the way a visual renders it are separate considerations.

Do not treat every blank as proof that the table is empty. A filter may have removed all matching rows, a relationship may not be carrying a filter as expected, or the visual may not be evaluating the measure in the context you anticipated. A useful diagnostic is to place the basic row-count measure in a card, then add filters or visual fields one at a time and observe when the result changes.

COUNTROWS compared with COUNT and COUNTA

Choose the counting function based on what you mean to count. COUNTROWS('Sales') counts records. COUNT('Sales'[Amount]) counts numeric values in the Amount column, so rows with blank amounts are not counted. COUNTA counts nonblank values in a column, including text values. These column functions answer a different question from a row count.

For instance, if Sales contains 20 records but three have a blank Amount, COUNTROWS('Sales') returns 20 while COUNT('Sales'[Amount]) returns 17, assuming the other Amount values are numeric. The smaller result is not a faulty row count; it reflects that the measure counts populated numeric cells. Use the function that matches the intended unit.

Counting unique entities is different again. If each customer can appear on multiple Sales rows, a row count is not a customer count. Use DISTINCTCOUNT('Sales'[CustomerID]) to count distinct customer IDs in the current context. Be aware that DISTINCTCOUNT includes a blank value as one distinct value when the column contains blanks; this can matter when reconciling results.

Count rows in a virtual table

The argument to COUNTROWS does not have to be a physical model table. It can be a DAX table expression, which makes the function useful for counting rows after filtering or grouping. For example, COUNTROWS(FILTER('Sales', 'Sales'[Amount] > 1000)) counts the rows returned by FILTER without requiring a separate calculated table.

You can also count the number of groups created by a table expression. For example, Customer Groups = COUNTROWS(VALUES('Sales'[CustomerID])) counts the distinct CustomerID values visible in the current context. This is broadly the purpose of a distinct count, but using a table expression can be helpful when the virtual table has more specific shaping or grouping logic. If blanks should not be included, account for them explicitly rather than assuming every distinct-count pattern excludes them.

Virtual-table patterns can be powerful, but they may be harder to read and can require more work from the model than a simple count. Prefer a basic measure when it expresses the requirement. If a complex expression is slow, test whether the logic can be simplified, whether the model’s relationships and storage are appropriate, and whether the visual is asking for an unnecessarily detailed result.

Common counting mistakes

A frequent mistake is assuming the measure counts a business object when it actually counts source rows. If the table contains multiple lines per order, a row count measures lines. If it contains multiple events per person, it measures events. Confirm the table’s grain and use a distinct count or another model-aware expression when the requested unit is unique orders, people, or products.

Another mistake is adding ALL automatically. A measure such as COUNTROWS(ALL('Sales')) deliberately removes filters from Sales, so it can ignore selections that users expect to affect the result. Use it only when an all-rows total is the intended result, and remember that filters from related tables and other parts of the expression require careful consideration.

Also check the model rather than changing the formula as a first response to unexpected numbers. Duplicate source records, blank keys, inactive relationships, and mismatched table granularity can all produce results that are mathematically correct but surprising. Compare a few filtered examples with the underlying records to establish what the measure is counting.

Frequently Asked Questions

What is the simplest DAX measure to count rows?
Use Row Count = COUNTROWS('TableName'), replacing TableName with the exact model table name. The measure counts rows visible in the current filter context.

Does COUNTROWS include rows with blank values?
Yes. It counts table rows, including rows where one or more columns are blank. It does not count only populated values in a particular column.

Why does my DAX row count show blank instead of zero?
COUNTROWS returns blank when its table argument has no rows. If the report should show zero in that situation, use COALESCE(COUNTROWS('Sales'), 0).

How do I count rows that match a condition?
Use CALCULATE(COUNTROWS('Sales'), 'Sales'[Status] = "Open") for a simple condition. Use FILTER inside the table expression when the condition requires more complex row-by-row logic.

Should I use COUNTROWS or DISTINCTCOUNT?
Use COUNTROWS to count records and DISTINCTCOUNT to count unique values in a column. If a customer appears on ten records, a row count includes ten records while a distinct customer count includes that customer once.

Choosing the right measure

Start by stating exactly what should be counted: records, nonblank values, or unique entities. Then select the matching function, check the table’s grain, and test the measure under the filters readers will use. For an ordinary record count, COUNTROWS is usually the clearest option; add CALCULATE for explicit conditions, and use COALESCE only when a displayed zero is preferable to blank.

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.