countx

COUNTX is a DAX (Data Analysis Expressions) function used in Power BI, Excel Power Pivot, and SQL Server Analysis Services. It counts the number of non-blank results produced by evaluating an expression for each row of a table. COUNTX is an iterator function that operates in a row context, returning a scalar value suitable for use in measures and calculations.

Syntax: COUNTX(table, expression)

Explanation: COUNTX iterates over every row in table, evaluates expression in the context of that row, and increments the count when the evaluated value is not BLANK. Note that values such as 0, FALSE, or empty strings are not BLANK and will be counted unless the expression explicitly returns BLANK for those rows.

Examples:

- COUNTX(Orders, Orders[OrderDate]) returns the number of orders with a non-blank order date.

- COUNTX(Products, Products[UnitPrice]) counts products that have a non-blank unit price.

- To count only rows meeting a condition, you can return BLANK for non-matching rows, e.g., COUNTX(Orders, IF(Orders[Quantity] > 0, 1, BLANK())). This counts orders with positive quantity.

Usage notes: COUNTX differs from COUNTROWS in that it bases the count on the result of an expression rather than on row presence alone. Because it evaluates an expression for each row, performance can be affected on very large datasets. It is commonly used inside measures and can be combined with CALCULATE or FILTER to refine results.