Downloads
DAXAggregationIntermediate

COUNTX

Evaluates an expression on each row of a table and counts the rows where the result is not blank.

Syntax
COUNTX(<table>, <expression>)

Returns: A whole number (a scalar).

What it does

COUNTX walks a table row by row, calculates an expression on each row, and counts the rows where the answer is not blank. It is useful when the thing you want to count depends on a calculation, such as "lines that made a loss" or "customers whose margin is below target." The expression is the test, and blank results are not counted.

Syntax and arguments

Example

The pricing analyst wants to know how many sales lines were sold below the cost of the goods, which signals a pricing error.

Loss Making Lines =
COUNTX(
    Sales,
    IF(Sales[SalesAmount] < Sales[TotalCost], 1)
)

IF returns 1 on loss making lines and a blank on the others, because the optional false argument is left out. COUNTX counts only the 1 results. The count can then be shown next to Total Sales.

Example: a second, different use

Count the products that have sales in the period, where a product with no sales returns a blank:

Products Sold =
COUNTX(
    VALUES(Product[ProductName]),
    IF([Total Sales] > 0, 1)
)

Each product is checked for its own sales. The count shows how many products are active under the current slicers.

Things to watch

In business terms

COUNTX answers questions like "how many invoices are overdue by more than 60 days," "how many employees are over their budget," and "how many contracts fall below the minimum margin." These counts go on the exception list that a controller reviews each month.

Related functions

All DAX functions · View as Markdown · DAX fundamentals