What is COUNTX in Power BI?

Jumping into DAX formulas can feel a bit intimidating, but Power BI’s COUNTX function is one of the most useful tools you can learn for more flexible data analysis. It goes beyond simple counting, letting you apply custom logic to sum up exactly what you need. This article will walk you through what COUNTX does, how to use it with practical examples, and how it differs from similar functions like COUNT and COUNTROWS.

Graphed

Still Building Reports Manually?

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

Watch the Demo

What is the COUNTX Function in Power BI?

At its core, COUNTX is an iterator function. This means it goes through a table row by row, performs a calculation or evaluation that you define (called an "expression"), and then counts how many rows returned a non-blank value for that expression.

Think of it like being a quality inspector on an assembly line. Instead of just counting every item that passes by (like the simpler COUNT function would), you have a specific checklist. You inspect each item one by one and only add a tally mark if it meets your criteria. COUNTX works the same way: it inspects each row with your formula and only counts it if it passes the test.

Its primary purpose is to give you a way to count rows based on a more complex condition than just checking if a column value exists.

Graphed

Still Building Reports Manually?

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

Watch the Demo

The Syntax of COUNTX

The DAX syntax for COUNTX is straightforward:

COUNTX(<table>, <expression>)

• <table>: This is the table you want the function to loop through, or a DAX expression that returns a table (like a FILTER or VALUES function).
• <expression>: This is the logic that COUNTX will evaluate for every single row of the specified table. If the result of the expression is anything other than blank, the row is counted.

The real power lies in that <expression> part. It allows you to embed calculations, logical tests, or even other DAX functions to create highly specific counting conditions.

Practical Example 1: Counting Products with High Sales Value

Let's start with a common business scenario. Imagine you have a Products table and a Sales table. You want to find out how many distinct products generated more than $5,000 in revenue.

A simple COUNT on the product column won't work because it doesn't know anything about sales. You need to first calculate the total sales for each product and then count only those that exceed the $5,000 threshold. This is a perfect job for COUNTX.

First, it’s good practice to create a base measure for total sales if you don't already have one.

Total Sales = SUM(Sales[SalesAmount])

This measure gives us the total sales amount, and it will automatically adjust to whatever context it's in (like a specific product in a table).

Now, let's build our COUNTX measure.

Step-by-Step Instructions:

1. In Power BI, right-click on a table in the Data pane and select New Measure.
2. Enter the following DAX formula:

High-Value Products = COUNTX( Products, IF( [Total Sales] > 5000, 1, BLANK() ) )

1. Press Enter to save the measure. Now you can add this to a card visual to see the final count.

Breaking Down the Formula:

• COUNTX(Products, ...): We tell Power BI to iterate through the Products table, one product at a time.
• IF([Total Sales] > 5000, 1, BLANK()): This is our expression - the test performed on each product.
• COUNTX then adds up all the times it got a "1" and ignores all the times it got a BLANK(), giving you the exact count of high-value products.

Graphed

Still Building Reports Manually?

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

Watch the Demo

Practical Example 2: Counting Customers with Multiple Purchases

Here’s another relatable problem. How many of your customers are repeat customers? To answer this, you need to count the number of orders for each customer and then count how many customers have more than one order.

This sounds tricky, but COUNTX simplifies it. For this example, assume you have a Customers table and a Sales table, with a relationship between them.

Step-by-Step Instructions:

1. Create a new measure in your model.
2. Enter the following DAX formula:

Repeat Customers = COUNTX( Customers, IF( COUNTROWS(RELATEDTABLE(Sales)) > 1, "Repeat", BLANK() ) )

1. Save the measure and drop it into a card visual. You’ll now see a count of all customers who have placed two or more orders.

Breaking Down the Formula:

• COUNTX(Customers, ...): This tells the function to go through your Customers table, one customer at a time.
• COUNTROWS(RELATEDTABLE(Sales)): This is the core of our expression.
• IF(..., "Repeat", BLANK()): The IF statement is our test. If the order count is greater than 1, it returns the text "Repeat" (which isn't blank). Otherwise, it returns BLANK().

Again, COUNTX only tallies up the rows where the expression returned a value, effectively counting only your repeat customers.

COUNTX vs. COUNT: What's the Difference?

It's easy to mix up COUNTX and COUNT, but they serve very different purposes.

• COUNT is a simple aggregation function. It counts the number of rows in a specified column that contain numbers, dates, or strings. It doesn't perform any row-by-row logic. It’s fast and efficient for basic counts.

Total Orders = COUNT(Sales[OrderNumber])

• COUNTX is an iterator. It evaluates an expression for each row in a table. You use it when your counting criteria depend on a calculation or a more complex logical condition that needs to be checked individually for each row.

Use COUNT for quick, straightforward counts on a single column. Use COUNTX when you need to "earn" the count by passing a test for each row.

Graphed

Still Building Reports Manually?

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

Watch the Demo

COUNTX vs. COUNTROWS with FILTER

This is where things can get interesting. Can you achieve the same results without COUNTX? Often, yes. A very common alternative pattern is using COUNTROWS with the FILTER function.

For our first example (High-Value Products), we could have written the formula like this:

High-Value Products (Alternative) = COUNTROWS( FILTER( Products, [Total Sales] > 5000 ) )

This formula does a similar thing:

• FILTER(Products, [Total Sales] > 5000): This first creates a new, temporary table containing only the products where the [Total Sales] measure is over $5,000.
• COUNTROWS(...): This then simply counts the number of rows in that newly filtered temporary table.

In many cases, both COUNTX and COUNTROWS(FILTER(...)) will produce the same result. The COUNTROWS(FILTER(...)) pattern is often more readable and sometimes performs better, so it's a great one to know. The best choice depends on the specific scenario and your personal preference for writing DAX.

Final Thoughts

Learning COUNTX opens up a whole new level of analytical capability in Power BI, allowing you to move from basic aggregations to nuanced, calculated counts. By iterating through tables and applying a logical expression row-by-row, it gives you the control to answer complex business questions with precision.

While mastering DAX functions like COUNTX is a powerful skill for any analyst, we know that sometimes you just need to get quick answers from your data without writing formulas. We created Graphed to connect directly to all your marketing and sales data sources - like Google Analytics, Salesforce, and Shopify - so you can use natural language to build dashboards and ask questions. It handles all the complex logic so you can get insights in seconds, not hours.