Guides

COUNT, COUNTA, DISTINCTCOUNT and COUNTROWS in DAX

By Ravindra Bagale, Power BI & Data Analytics trainer · LinkedIn · Published: 30 September 2026 · Last updated: 30 September 2026

COUNT, COUNTA, COUNTROWS and DISTINCTCOUNT are not four names for the same total — each one answers a different grain, and blanks move the result.

Orders = DISTINCTCOUNT ( Sales[Order ID] )
Lines = COUNTROWS ( Sales )

Why each count, why FILTER, what comes out

Same three Pune rows:

Why you pick one function:

1. COUNTROWS ( Sales ) answers "how many lines are in the filter?" Result: 3.
2. DISTINCTCOUNT ( Sales[Order ID] ) answers "how many orders?" Result: 2. Order A is one order with two lines.
3. COUNT ( Sales[Amount] ) answers "how many numeric amounts are filled?" Result: 3, because none are blank.
4. COUNTA ( Sales[Channel] ) answers "how many channel cells are not blank?" Result: 3. Use this when the column is text.
5. If Amount on the middle row is blank, COUNT becomes 2 and COUNTBLANK becomes 1. COUNTROWS stays 3. The row is still there.

Why FILTER shows up with a count:

1. COUNTROWS ( Sales ) counts every row the slicer left. It does not apply a second test.
2. "How many lines are above 50?" needs a test. FILTER keeps rows. COUNTROWS turns the kept rows into one number.
3. Rows in: 3. Test: Amount > 50. Rows kept: 100 and 60. Number out: 2.
4. DISTINCTCOUNT does not need FILTER when you only want unique IDs on the rows you already have.
5. Do not FILTER the whole Sales table just to say Channel = Online. A column filter inside CALCULATE is the lighter way. Use FILTER when the test is a measure, or a rule a simple column filter cannot say.

What you should say before you write it: lines, filled cells, or unique orders. Then the function is obvious.

Real example: slips, orders, and a missing phone bill

Same FreshBasket counters. In Mumbai this week the book looks like this:

Why the count changes with the question:

1. Rows in: 3 lines. COUNTROWS out: 3. That is slips, not customers.
2. DISTINCTCOUNT of Order ID out: 2. M-1 is one order with two lines. The owner did not get three orders.
3. COUNT of Amount out: 3, because every amount is filled.
4. Now the phone bill book for the Mumbai shop: Jan 499, Feb is still blank because nobody typed it, Mar 699. Rows in: 3. COUNT of the bill amount out: 2. COUNTBLANK out: 1. COUNTROWS stays 3. The blank month is still a row.

Why FILTER: "how many Mumbai lines are above 250?" FILTER keeps rice 500 and atta 300. COUNTROWS out: 2. Oil 200 is gone. You did not need FILTER to count all lines. You need it when there is a second test on top of the city slicer.

Click Pune and repeat the sentence out loud before you trust the card: lines, orders, or blank bills.

What do I need before this guide?

• Measures vs calculated columns.
• SUM vs SUMX so “aggregate a column” already feels normal.
• Course: Aggregation functions.

Before and after (look at the tables first)

The FreshBasket table (fictional)

Picture five rows:

1. Order A, two lines, both with delivery minutes.
2. Order B, three lines, one delivery minute blank.
3. That is 5 rows, 2 orders, 4 numeric delivery times, 1 blank.

If the card says 5 and the manager asked for orders, the measure used the wrong function. The visual is innocent.

The four functions, side by side

Order Lines = COUNTROWS ( Sales )

Orders = DISTINCTCOUNT ( Sales[Order ID] )

Times Filled = COUNT ( Sales[Delivery Mins] )

Channels Filled = COUNTA ( Sales[Channel] )

Times Missing = COUNTBLANK ( Sales[Delivery Mins] )

Read each line:

1. COUNTROWS does not care which columns are blank. It counts rows that survived the filter context.
2. DISTINCTCOUNT counts each Order ID once. Blank Order ID, if you had one, would count as one extra distinct value.
3. COUNT counts non-blank numbers. A text column is the wrong argument.
4. COUNTA counts non-blank values even when the column is text, such as Channel.
5. COUNTBLANK is the question “how many are missing?”, not “how many do we have?”.

Where people mix them up

1. They drop Sales[Order ID] into a visual and let Power BI choose an implicit count. Write an explicit measure so the name says Lines or Orders.
2. They average delivery time with AVERAGE on the line grain, so a five-line order weighs five times. That is a grain problem, same family as counting.
3. They use COUNTROWS of the whole Sales table on a card that sits next to a City slicer and then forget the slicer is on. The measure is filtered. Check the slicer before you call the number wrong.
4. They DISTINCTCOUNT a column that is already unique per row, so it matches COUNTROWS and they think the functions are identical. Change the column to Order ID and they split.

A small pattern you will reuse

“How many cities sold more than a threshold?” is not DISTINCTCOUNT of City on the fact table alone. You need cities where a measure passes a test. That is COUNTROWS plus FILTER, in the next pattern guides. For this lesson, stay on the column and the row.

Mistakes and calm fixes

Ghabru naka — write the word “lines” or “orders” in the measure name so the card cannot lie politely.