COUNTBLANK
Counts the cells in a column that are blank.
COUNTBLANK(<column>)Returns: A whole number (a scalar). Zero when no cell is blank.
What it does
COUNTBLANK counts how many cells in a column are empty. It is the reverse of COUNTA, which counts the filled cells. Use it to measure gaps: missing dates, missing cost centers, or missing amounts that would drop out of a report without any warning.
Syntax and arguments
- column: a column reference such as
Sales[TotalCost]. Only one column is accepted.
Example
The close team wants to know how many sales lines have no cost recorded, because those lines make the margin look better than it is.
Lines Missing Cost = COUNTBLANK(Sales[TotalCost])
A large number points to a feed problem. Place the measure on a card next to the total number of lines, so the reader can see the share of lines that are missing a cost.
Example: a second, different use
Missing customer keys on sales, which would mean the sale cannot be assigned to a customer:
Sales Without Customer = COUNTBLANK(Sales[CustomerKey])
Each blank key is a sale that appears in total sales but not in any customer report.
Things to watch
- Only true blanks are counted. A zero is not blank, so a cost of 0 is invisible to this measure. Use COUNTROWS with a filter for zeros.
- The count reflects the current filter context. Filter the report to one month to see the gaps for that month only.
- COUNTBLANK counts cells, not rows. Use it on the key columns you need.
- A blank in a related table can hide behind a relationship. Check the keys on both sides.
- Do not use COUNTBLANK as a substitute for a proper reconciliation. It tells you that something is missing, not what should be there.
In business terms
COUNTBLANK supports data quality reviews before a close or an audit. Finance teams use it to count uncoded transactions, unmatched bank lines and receivables without a due date. Each blank is a question someone has to answer.