How to Use COUNTX in Power BI

Need to count rows in Power BI, but only when they meet specific criteria? The DAX function COUNTX is the tool you're looking for. Unlike simpler counting functions, COUNTX gives you the power to evaluate an expression for each row in a table and then count only the rows where your expression returns a result. This article will walk you through what COUNTX is, how to use it with practical examples, and some best practices to keep in mind.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

What Exactly is COUNTX and When Should You Use It?

In Power BI, you have a few options for counting things. Functions like COUNT and COUNTA are great for simple counts. COUNT tallies up rows containing numbers, while COUNTA tallies rows containing any kind of data (numbers, text, etc.). They're quick and easy.

But what if you need more nuance? What if you want to count only the sales orders that are over $1,000? Or count the number of products in your inventory that have a profit margin below 20%? For this, you need a function that can first evaluate a condition for each row and then count based on the outcome. That's precisely what COUNTX does.

COUNTX is an "iterator" function. This fancy term simply means it goes through a table row by row, performs a calculation or logical test that you define, and then counts how many rows produced a non-blank result. This row-by-row evaluation is what makes it so powerful and flexible.

You should use COUNTX when your counting logic depends on a calculation involving multiple columns for each row, such as:

• Counting orders with a total value (price * quantity) above a certain threshold.
• Counting employees who have accumulated more than 10 days of paid time off.
• Counting survey responses where a respondent's age is over 30 and they answered "Yes" to a specific question.

For simple, single-column conditions (like counting "Electronics" in a 'Category' column), you might use another function like CALCULATE, but COUNTX is your go-to for more complex row-level logic.

Understanding the COUNTX Syntax

The syntax for COUNTX is straightforward, consisting of two main parts: the table you want to scan and the expression you want to evaluate.

COUNTX(<table>, <expression>)

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Breaking it down:

• <table>: This is the table the function will iterate over. It can be an existing table in your data model (e.g., 'Sales') or a virtual table created by another function, most commonly the FILTER function.
• <expression>: This is the calculation or logical test that will be performed for every single row in the specified <table>. COUNTX will tally every row where this expression does not return a blank value.

Practical Examples: Putting COUNTX to Work

Let's move from theory to practice. Imagine we have a simple 'Sales' table with the following columns: OrderID, Product, Category, Quantity, UnitPrice, and ShipDate.

Example 1: Counting High-Value Orders

Our first goal is to count the number of orders where the total sale value (which is Quantity multiplied by UnitPrice) is greater than $500. This calculation needs to happen for each row, making it a perfect job for COUNTX.

To do this, you would create a new "Measure" in Power BI.

Method 1: Using IF and BLANK()

A common pattern with COUNTX is to pair it with an IF function. The IF function checks our condition and returns a value (like 1) if true, and BLANK() if false. Since COUNTX skips blanks, it effectively counts only the rows that meet our condition.

In Power BI, click "New Measure" and enter this DAX formula:

High Value Orders = COUNTX( Sales, IF([Quantity] * [UnitPrice] > 500, 1, BLANK()) )

Here's what happens: DAX iterates through every row of the 'Sales' table. For each row, it multiplies [Quantity] by [UnitPrice]. If the result is over 500, the IF statement returns a 1. Otherwise, it returns BLANK(). Finally, COUNTX counts all the 1s and ignores the blanks, giving you a total of your high-value orders.

Method 2: Using the FILTER Function

Another, often more intuitive way to achieve the same result is to use the FILTER function within COUNTX. This method first creates a temporary, filtered table containing only the rows meeting our condition, and then COUNTX counts the rows of that new table.

Create a new measure with this formula:

High Value Orders (Filtered) = COUNTX( FILTER( Sales, Sales[Quantity] * Sales[UnitPrice] > 500 ), Sales[OrderID] )

Here's the breakdown:

1. FILTER(Sales, Sales[Quantity] * Sales[UnitPrice] > 500) first creates a virtual table of all sales over $500.
2. COUNTX then iterates through this much smaller, pre-filtered table.
3. For the second argument, Sales[OrderID], we just need to specify any column to count. Since the table is already filtered, every row in it is a match.

While both methods work, the FILTER approach is often easier to read and can be more performant on very large datasets.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Example 2: Counting Electronics Orders

Let's say you want a simple count of all orders in the "Electronics" category. While CALCULATE(COUNTROWS('Sales'), 'Sales'[Category] = "Electronics") is the most efficient way to do this, let's see how it's done with COUNTX to understand the underlying mechanics.

We'll use the FILTER pattern again. Create a new measure:

Electronics Orders = COUNTX( FILTER( Sales, Sales[Category] = "Electronics" ), Sales[OrderID] )

This formula works just like our previous filtered example. It first isolates all the rows where the category is "Electronics" and then simply counts them. This reinforces the concept that COUNTX operates over the table provided in its first argument - whether that table is your complete data model or a smaller, virtual one you create on the fly.

Example 3: Counting Orders with Delayed Shipping

Imagine your 'Sales' table also has an OrderDate and a ShipDate column. You want to identify supply chain issues by counting every order that took more than three days to ship.

This requires a row-by-row date calculation - another perfect use case for COUNTX.

Create a new measure with this formula:

Delayed Shipments = COUNTX( Sales, IF( [ShipDate] - [OrderDate] > 3, 1, BLANK() ) )

Just like our first example, this formula iterates over the entire 'Sales' table. For each row, it subtracts the OrderDate from the ShipDate. If the difference is a value greater than 3, it counts that row. This is a powerful way to implement business logic that depends on date math, something simple counting functions can't handle.

Common Pitfalls and Best Practices

While powerful, COUNTX can be tricky. Here are a few things to watch out for to keep your reports accurate and fast.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Mistake 1: Using COUNTX When COUNTROWS is Enough

A simple phrase to remember is: "Don't iterate if you don't have to." If all you need is a count of rows from a filtered table, COUNTROWS(FILTER(...)) is often more direct and performant than a COUNTX(FILTER(...), ...). Use COUNTX when your second argument needs to perform a calculation that can't be done in a simple FILTER clause.

Better alternative for our "Electronics" example:

Electronics Orders = COUNTROWS(FILTER(Sales, Sales[Category] = "Electronics"))

Mistake 2: Forgetting That FILTER is a Table

New users sometimes get confused by nesting functions. Remember, the first argument in COUNTX must always be a table. The FILTER function returns a table, which is why COUNTX(FILTER(...), ...) works perfectly.

Best Practice: Prioritize Readability

DAX formulas can get complicated. Choosing the clearest method is often the best choice, especially if you're working on a team. Using variables with VAR and RETURN can make complex COUNTX measures much easier for others (and your future self) to understand.

Best Practice: Be Mindful of Performance

Because COUNTX is an iterator, it can be slow on tables with millions of rows. It has to check the expression for every single row. If you can achieve the same result with a combination of CALCULATE and other non-iterator functions, that approach will almost always be faster.

Final Thoughts

Mastering COUNTX is a big leap forward in your Power BI journey. It unlocks the ability to define custom business logic and answer complex questions by evaluating data row-by-row. By understanding its structure and knowing when to use it over simpler functions, you can create far more insightful and dynamic reports.

Learning DAX is rewarding, but sometimes you don't have time to write formulas to get a simple answer. That's our focus at Graphed. We connect directly to your data sources, letting you ask questions like, "how many orders over $500 did we get from Facebook ads last month?" using natural language. We instantly build the dashboard for you, so you can spend less time grappling with DAX and more time discovering insights that grow your business.