DAX Group By Count Rows: Measures, Tables, and Examples

How-to basics · Updated November 3, 2026

To group records and count their rows in DAX, use SUMMARIZECOLUMNS with COUNTROWS, for example: SUMMARIZECOLUMNS('Product'[Category], "Row Count", COUNTROWS('Sales')). For a report visual, the simpler choice is usually a measure containing COUNTROWS('Sales'), with the visual’s fields providing the grouping.

The basic DAX group-and-count pattern

DAX does not have a standalone SQL-style GROUP BY clause. Instead, a table function creates the groups, and an expression calculates a result for each group. SUMMARIZECOLUMNS is a common modern choice: put the columns to group by first, then provide a name and expression for each calculated result.

For example, suppose a model has a Sales fact table and a related Product table. To count Sales table rows for each product category, the expression is:

SUMMARIZECOLUMNS('Product'[Category], "Row Count", COUNTROWS('Sales'))

The first argument defines the grouping. The text "Row Count" becomes the name of the output column, and COUNTROWS('Sales') returns the count for each category. The result is a table with one row per category that has a nonblank result, plus the calculated count.

Use the actual table and column names from your model. The example assumes a relationship allows a category filter from Product to filter Sales. Without a suitable relationship, the category may not affect the Sales count, so the same total can appear for multiple groups.

Count rows in a report visual with a measure

If the goal is a table or matrix in Power BI, a measure is generally more useful than creating a separate grouped table. Create this measure:

Sales Row Count = COUNTROWS('Sales')

Add Product Category to the visual’s rows and Sales Row Count to its values. The visual groups the measure by category automatically. Add a date, region, or other field to the visual and the same measure is recalculated for each resulting group. Slicers and report filters also change the filter context and therefore the count.

This measure counts rows in Sales, not unique customers, orders, or products. If one order occupies several Sales rows because it has multiple line items, the measure counts each line-item row. That can be exactly right for counting units or transactions at line level, but it is not the same as counting distinct order numbers.

A measure is dynamic: it responds to the filters active when a visual is evaluated. That makes it a better fit for interactive reports than a calculated table, which is materialized when the model refreshes and does not recalculate for each slicer selection.

Write a DAX query for grouped counts

For a DAX query, such as one run in DAX Studio or a model’s query window, use EVALUATE before the table expression. A query for category counts looks like this:

EVALUATE SUMMARIZECOLUMNS('Product'[Category], "Row Count", COUNTROWS('Sales'))

EVALUATE tells the query engine to return the table produced by the expression. It is not part of a measure definition. In a query editor, format the expression across several lines if that makes the grouping columns and calculations easier to inspect.

To group at a more detailed level, include another grouping column. For instance, add 'Date'[Year] after 'Product'[Category] to return a count for each category-and-year combination. The number of output groups can grow quickly when you add columns, particularly when you combine fields with many distinct values.

A DAX query can also be useful for checking whether a report result matches the model’s data. Remember that query results depend on the model and any filters or query clauses supplied. A query is a way to return a table for inspection; it does not create a permanent model table by itself.

Create a calculated table of counts

If you need a grouped result stored in the model, create a calculated table using SUMMARIZECOLUMNS. For example, a calculated table expression could be:

Category Counts = SUMMARIZECOLUMNS('Product'[Category], "Row Count", COUNTROWS('Sales'))

Calculated tables are evaluated during data refresh or recalculation, not every time a report user changes a slicer. Use one when a stored summary table is genuinely useful for a model design or downstream calculation. Do not choose it merely to make a visual show grouped counts; a measure usually responds to report filters more naturally.

There is another practical consideration: the summary table has its own columns and relationships, and it may not behave like the original fact table in every visual. Decide how it should relate to the rest of the model and whether its values should remain fixed between refreshes before adding it.

Count by multiple columns and understand filter context

Grouping by more than one field means counting rows for each combination of field values. For example, grouping by category and year gives one result for each category-year pair, not one count per category followed by a separate count per year. Use columns from dimensions when possible, because they provide clear labels and can filter the fact table through the model’s relationships.

In SUMMARIZECOLUMNS, each group creates a filter context for the expressions in the result. A category group filters the calculation to that category when the model relationship carries the filter to Sales. A report filter can narrow it further, so a count for one year may differ from the count for all years.

If the grouping column comes from an unrelated table, it may not filter Sales. In that case, the count can repeat for every displayed value. Check the relationship direction and the model’s relationship path before assuming the grouping function is at fault. DAX results follow the model’s filter behavior.

Choose between COUNTROWS and other count functions

COUNTROWS is the clearest function when the request is literally to count rows in a table. It counts rows that remain in the current filter context. Other count functions answer different questions, so choosing one based only on the word “count” can produce an incorrect result.

  • COUNTROWS('Sales') counts rows in Sales, including rows where a particular column is blank.
  • COUNT('Sales'[Order Number]) counts numeric values in that column, not every row; it does not count text values.
  • COUNTA('Sales'[Order Number]) counts nonblank values, including text, in the selected column.
  • DISTINCTCOUNT('Sales'[Order Number]) counts distinct values, so repeated order numbers count once. Blank is counted as a distinct value unless you exclude it.

For example, if an order has four product lines, COUNTROWS('Sales') may return four while DISTINCTCOUNT('Sales'[Order Number]) returns one for that order group. Decide whether the unit being counted is a physical table row, a populated field, or a unique business identifier.

Common problems and how to diagnose them

A count that repeats for every group often points to a missing or inactive relationship, an unexpected relationship direction, or a grouping column that cannot filter the counted table. Test the relationship by placing a relevant dimension field and a basic row-count measure in a visual. Review the model view and confirm the intended path between the dimension and fact table.

A count that is too high may be a legitimate row-level count rather than a distinct entity count. Check whether the fact table contains multiple rows per order, customer, or other identifier. If the requirement is “number of orders,” use a distinct count of the order key instead of assuming each row represents one order.

Another point of confusion is an empty result. COUNTROWS returns blank when its table argument has no rows. In a SUMMARIZECOLUMNS result, groups whose calculated expressions are all blank can be omitted. If a visual or query must display zero for an empty group, a measure can convert the blank result to zero with COALESCE(COUNTROWS('Sales'), 0). Whether zero-valued groups appear also depends on the visual and its handling of categories with no data.

Finally, avoid mixing up the output of a query with a reusable measure. A query uses EVALUATE and returns a table. A measure returns a scalar value in the current context and is intended for report calculations. A calculated table stores a table in the model at refresh time. Each has a different purpose even when all three show grouped counts.

Frequently Asked Questions

Does DAX have a GROUP BY statement?
No. DAX uses table functions such as SUMMARIZECOLUMNS to define groups and calculate an expression for each group. A DAX query commonly starts with EVALUATE, but EVALUATE is not a GROUP BY clause.

What is the simplest way to count rows by category in Power BI?
Create a measure with COUNTROWS('Sales'), then put the category field in a table or matrix visual and the measure in Values. The visual supplies the groups, and the measure responds to report filters.

Does COUNTROWS include rows with blank values?
Yes. COUNTROWS counts rows in the table expression; it does not require a particular column to contain a value. Use a nonblank-value count when the question is specifically how many rows have a populated field.

How do I count unique orders instead of sales rows?
Use DISTINCTCOUNT on the order-number column, such as DISTINCTCOUNT('Sales'[Order Number]). This counts distinct order numbers in the current filter context rather than counting every line in the Sales table.

Why does my count repeat for every group?
Usually, the grouping field is not filtering the table being counted. Check for a valid relationship between the dimension and fact tables, and confirm that the field you grouped by comes from the intended table.

Which approach should you use?

Use a measure for interactive report visuals, a SUMMARIZECOLUMNS expression inside a DAX query when you need to inspect or export grouped results, and a calculated table only when a refresh-time summary stored in the model is needed. In each case, begin by defining what one row represents and whether the requirement is to count rows or distinct entities. Then verify that relationships and filters make the groups apply to the intended table. That small check prevents many apparently mysterious DAX count errors.

Related

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.