Power BI DAX Count Functions: COUNT, COUNTA, COUNTX, COUNTROWS and DISTINCTCOUNT

A sales manager once asked me why two reports showed a different number of sales for the same month. One report used COUNT(Sales[Sales Id]), the other DISTINCTCOUNT(Sales[Sales Id]), and some orders had been split into several rows with the same ID. Both numbers were correct; they were just answering different questions.

DAX has eight functions that count things, and they differ in small but important ways: what types of values they count, whether blanks are included, and whether they count values, rows or unique values. In this tutorial, I’ll explain each one with an example, and then show you how to choose between them.

Quick answer: Use COUNTROWS to count the rows of a table, for example COUNTROWS(Sales); Microsoft recommends it whenever you’re counting rows. Use COUNT or COUNTA to count the non-blank values in a column, DISTINCTCOUNT to count unique values, COUNTBLANK to count blanks, and COUNTX or COUNTAX to count the results of an expression over a table.

The DAX count functions at a glance

Function	What it counts	Blanks
COUNT(column)	Values in a column: numbers, dates and text, but not TRUE/FALSE	Skipped
COUNTA(column)	Values in a column, including TRUE/FALSE	Skipped
COUNTX(table, expression)	Rows where the expression gives a number, date or text	Skipped
COUNTAX(table, expression)	Rows where the expression gives any non-blank result, including TRUE/FALSE	Skipped (an empty string counts)
COUNTBLANK(column)	Blank cells in a column	Counted
COUNTROWS([table])	Rows in a table	Irrelevant: every row counts
DISTINCTCOUNT(column)	Unique values in a column	Blank counts as one value
DISTINCTCOUNTNOBLANK(column)	Unique values, ignoring blank	Skipped

All of them return a whole number, and most return BLANK rather than 0 when there are no rows to count, which matters when you use them in visuals. You’ll find the full details for each function in the DAX function reference.

COUNT

COUNT counts the cells in a column that contain a value. It works with numbers, dates and text, but not with TRUE/FALSE columns, and it skips blanks. For example, this measure counts the sales IDs in a sales table:

Number of sales id = COUNT ( Data1[Sales Id] )

The table has eight rows, so the result is 8, even though some IDs appear twice. COUNT doesn’t care about duplicates; it only counts filled-in cells.

COUNTA

COUNTA works like COUNT, but it also counts TRUE/FALSE values. In practice, that’s the only difference, so I use COUNTA when the column might be Boolean and COUNT otherwise.

COUNTAX

COUNTAX goes through a table row by row, works out an expression for each row, and counts the results that aren’t blank. Use it when what you want to count isn’t a column but a calculation. One detail to know: an expression that returns an empty string counts as non-blank.

COUNTBLANK

COUNTBLANK counts the blank cells in a column. It’s handy for data-quality checks, for example to see how many orders are missing a region. Note that 0 isn’t blank, so zeros aren’t counted. If a column has no blanks, the result is 0; if there are no rows at all, the result is BLANK.

COUNTROWS

COUNTROWS counts the rows of a table. You can give it a table name, such as COUNTROWS(Sales), or a table expression, which is where it becomes really useful: COUNTROWS(FILTER(Sales, Sales[Region] = "AAA")) counts only the sales in one region. The table argument is optional; without it, COUNTROWS counts the rows of the table the measure belongs to.

COUNTX

COUNTX is the row-by-row version of COUNT. It evaluates an expression for each row of a table and counts the rows where the result is a number, date or text. If you need to count TRUE/FALSE results, use COUNTAX instead.

DISTINCTCOUNT and DISTINCTCOUNTNOBLANK

DISTINCTCOUNT counts the unique values in a column, and it treats BLANK as a value of its own. In the example below, the Amount column contains 1000 twice, 2000, 4000 and one blank, so the result is 4: three different amounts plus the blank (DISTINCTCOUNT reference).

DISTINCTCOUNTNOBLANK does the same but ignores the blank, so for the same amounts it would return 3. Choose it when a blank isn’t a meaningful value, such as a missing customer ID.

COUNT, COUNTA and COUNTX with different data types

To see how the functions handle each type, I used a small table with an ID, a Product name, an Amount column with one blank, a Boolean column and a Sales Date column:

COUNT

COUNT works on the number column, COUNT(Data[ID]), the date column, COUNT(Data[Sales Date]), and the text column, COUNT(Data[Product name]). In each case, it counts the cells that aren’t blank:

COUNTA

COUNTA gives the same results for numbers, text and dates, and it also works on the Boolean column, where COUNT can’t be used. On the Amount column, it returns 4, because one amount is blank:

COUNTX

COUNTX needs the table as well as the column, for example COUNTX(Data, Data[Amount]). It gives the same results as COUNT for numbers, dates and text:

Its real strength is that the first argument can be a filtered table. This measure counts only the rows where the amount is 1000, and returns 2:

CountX = COUNTX ( FILTER ( Data, Data[Amount] = 1000 ), Data[Amount] )

COUNT vs DISTINCTCOUNT

This is the difference behind the sales manager’s question at the start. In a sales table where some orders span several rows, COUNT counts every filled-in Sales Id cell, while DISTINCTCOUNT counts each ID once:

Count of id = COUNT ( Data1[Sales Id] )

Distinct Count of id = DISTINCTCOUNT ( Data1[Sales Id] )

Neither is wrong. COUNT answers “how many sales rows are there?” and DISTINCTCOUNT answers “how many different orders are there?”, so pick the one that matches the question your report is asking.

COUNTROWS vs COUNT: which should you use?

When you want to count the rows of a table, you could count a column that’s always filled in, or count the rows directly. Using this sales table:

COUNT(Data1[Sales Id]) and COUNTROWS(Data1) both return 8:

They agree only because Sales Id has no blanks. Microsoft’s DAX guidance recommends always using COUNTROWS when you mean to count rows, for three reasons: it’s more efficient, it isn’t affected by blanks in any column, and the intention of the formula is clearer. I follow that rule in every model I build.

Example 1: Count how many times each value occurs

Sometimes you want a column that shows, on every row, how many times its Sales Id appears in the table. Using the same sales table:

Add a calculated column from Modeling > New column. The classic formula uses EARLIER to refer to the current row’s value:

Occurrence =
COUNTX (
    FILTER ( Data1, EARLIER ( Data1[Sales Id] ) = Data1[Sales Id] ),
    Data1[Sales Id]
)

IDs 123 and 122 appear twice, so their rows show 2; the others show 1. It works, but these days I write it with a variable, which Microsoft’s guidance on variables suggests as an easier-to-read alternative to EARLIER:

Occurrence =
VAR CurrentId = Data1[Sales Id]
RETURN
    COUNTROWS ( FILTER ( Data1, Data1[Sales Id] = CurrentId ) )

Example 2: Count distinct values that meet a condition

In this list of items, each with an ID and a status, I wanted to know how many different items are still open:

CALCULATE applies the status filter, and DISTINCTCOUNT counts the items that are left:

Open Items =
CALCULATE (
    DISTINCTCOUNT ( Sheet[Item Id] ),
    Sheet[Status] = "Open"
)

The result is 3, one for each open item.

Example 3: Count employees by month

HR teams often want a head count per month: everyone hired before the month started, who hadn’t left before it ended. This example uses an HR table with each employee’s hire date and termination date:

It also needs a date table. You can create one as a calculated table with this formula, or see how to create a date table in Power BI for other ways:

Dates =
ADDCOLUMNS (
    CALENDAR ( DATE ( 2026, 1, 1 ), DATE ( 2026, 12, 31 ) ),
    "Year", YEAR ( [Date] ),
    "Month", FORMAT ( [Date], "MMMM" ),
    "Month #", MONTH ( [Date] ),
    "Year Month", YEAR ( [Date] ) & " " & FORMAT ( [Date], "MMMM" ),
    "Year Month #", YEAR ( [Date] ) * 100 + MONTH ( [Date] )
)

Then create this measure. FILTER keeps the employees who were active during the month, and COUNTROWS counts them:

Head Count =
COUNTROWS (
    FILTER (
        'HR Table',
        DATE ( YEAR ( 'HR Table'[Hire date] ), MONTH ( 'HR Table'[Hire date] ), 1 )
            <= FIRSTDATE ( Dates[Date] )
            && (
                ISBLANK ( 'HR Table'[Terminated date] )
                    || EOMONTH ( 'HR Table'[Terminated date], 0 ) >= LASTDATE ( Dates[Date] )
            )
    )
)

Put the Year Month column from the date table on a visual’s axis, add the measure, and you get the number of active employees for each month.

Example 4: Count the number of sales

Finally, the simplest case: counting sales in a report. With the sales table below, a measure that counts the Sales Id column gives the number of sales rows, 8:

Number of sales id = COUNT ( Data1[Sales Id] )

Following Microsoft’s advice above, COUNTROWS(Data1) is the better way to write this, and DISTINCTCOUNT(Data1[Sales Id]) is the right choice if you want the number of different orders instead.

How to choose the right count function

To count rows, use COUNTROWS, with FILTER or CALCULATE when you need a condition. To count unique values, use DISTINCTCOUNT, or DISTINCTCOUNTNOBLANK if blank shouldn’t count as a value. To count filled-in cells in one column, use COUNT, or COUNTA for TRUE/FALSE columns. To count missing values, use COUNTBLANK. And reach for COUNTX or COUNTAX only when you need to count the result of an expression row by row.

For conditional counts in more detail, see Power BI measure count with filter and COUNTIF in a Power BI measure.

These Power BI DAX tutorials go well with the count functions:

• Countif function in Power BI Measure + 10 Useful Examples
• Power bi measure examples (20 useful examples)
• Power BI SUM Measure | SUMX() in Power BI
• Power bi measure switch statement with examples
• Power bi show items with no data

Frequently Asked Questions