What is COUNTAX in Power BI?

If you're delving into Power BI, you've quickly realized that DAX (Data Analysis Expressions) is the engine that powers your reports. One of the most useful but sometimes confusing functions you'll encounter is COUNTAX. Unlike a simple COUNT, COUNTAX gives you the power to perform a calculation first and then count the results. This tutorial will break down what COUNTAX is, how it works, and show you practical examples to put it into action.

Graphed

Still Building Reports Manually?

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

Watch the Demo

Understanding the Basics: COUNT vs. COUNTA vs. COUNTAX

Before jumping into COUNTAX, it's helpful to understand its simpler siblings, COUNT and COUNTA. Getting the distinction clear in your head makes COUNTAX much easier to grasp.

• COUNT: This is the most basic counting function. It simply counts the number of cells in a column that contain numbers. It ignores blank cells, text, and other non-numeric data types.
• COUNTA: This function is a bit more inclusive. It counts the number of cells in a column that are not empty (blank). It will count numbers, text, dates, etc. - basically anything except an empty cell.
• COUNTAX: This is an "iterator" function and the real star of the show. It works by going through a table row by row, evaluating an expression you provide for each row, and then counting the total number of rows where the expression's result is not blank. The "X" at the end of many DAX functions signals that it's an iterator.

The key takeaway is that COUNTAX doesn't just count values that are already in a column, it counts the results of a calculation you define.

Breaking Down the COUNTAX Syntax

The syntax for a DAX function can look intimidating at first, but COUNTAX is quite straightforward once you understand its two components.

COUNTAX(<table>, <expression>)

Let’s look at each part:

• <table>: This is the table you want the function to iterate over, or "loop through." You can use the name of an existing table or a function that returns a table (like FILTER or VALUES).
• <expression>: This is the calculation or logical test that will be performed for each individual row of the specified table. COUNTAX will count a row if the result of this expression is anything other than blank.

Think of it as a two-step process: First, the function goes through your table, running your calculation on each row. Second, it looks at all the results from step one and counts how many of them are not blank.

Graphed

Still Building Reports Manually?

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

Watch the Demo

Practical Examples of Using COUNTAX

Theory is great, but let’s see how COUNTAX solves real-world analysis problems. Imagine we have a simple sales table named SalesData with the following columns: OrderID, Product, Category, UnitPrice, Quantity, and Region.

Example 1: Counting High-Value Orders

Goal: We want to count how many individual order lines (not entire orders) have a total value greater than $200.

First, we need to calculate the value for each line, which is UnitPrice * Quantity. Then, we need to check if that value is over $200 and count how many times that happens. This is a perfect use case for COUNTAX.

You would create a new measure with the following DAX formula:

High Value Order Lines = COUNTAX( 'SalesData', IF('SalesData'[UnitPrice] * 'SalesData'[Quantity] > 200, 1, BLANK()) )

How it Works:

• The 'SalesData' argument tells COUNTAX to go through the entire SalesData table one row at a time.
• The IF(...) statement is our expression. For each row, it calculates UnitPrice * Quantity and checks if it's greater than 200.
• If the condition is true, the expression returns the number 1.
• If the condition is false, the expression returns BLANK().
• Finally, COUNTAX counts all the 1s it received and ignores all the BLANKs, giving us the total number of high-value order lines.

Graphed

Still Building Reports Manually?

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

Watch the Demo

Example 2: Counting Products Currently in Stock

Goal: Let's say we have an Inventory table with Product and StockLevel columns. We want to count how many distinct products have a stock level greater than zero.

Here, we want to iterate over our inventory list and count only the products that are actually available.

Create a new measure using this DAX formula:

Products In Stock = COUNTAX( FILTER('Inventory', 'Inventory'[StockLevel] > 0), 'Inventory'[Product] )

How it Works:

• Here, the <table> argument is a bit more complex. Instead of the whole Inventory table, we use the FILTER function to create a temporary, virtual table that only includes rows where StockLevel is greater than 0.
• COUNTAX now iterates over this smaller, pre-filtered table.
• The <expression> is simply 'Inventory'[Product]. For each row in our filtered "in stock" table, it returns the product name.
• Since none of the product names in this filtered table will be blank, COUNTAX counts every row, giving us the number of products that are in stock.

You might notice that COUNTROWS(FILTER('Inventory', 'Inventory'[StockLevel] > 0)) would achieve the same result in this specific scenario. However, getting comfortable with COUNTAX's structure helps you solve more complex problems where the expression needs to do more work.

Example 3: Counting Customers with Transactions in a Specific Region

Goal: We want to find out how many unique customers made a purchase in the "West" region. This combines filtering with counting unique values, a common analysis task.

DAX gives us a few ways to solve this, but here’s how you could approach it with COUNTAX and other functions:

Customers in West Region = COUNTAX( FILTER( VALUES('SalesData'[CustomerName]), CALCULATE(COUNTROWS('SalesData')) > 0 ), 'SalesData'[Region] = "West" )

Note: A more direct way to solve this is often CALCULATE(DISTINCTCOUNT('SalesData'[CustomerName]), 'SalesData'[Region] = "West"). This example is to illustrate a thinking process with iterators.

This looks complex, but it highlights another way of thinking that is core to DAX. Mastering iterator logic is essential.

Graphed

Still Building Reports Manually?

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

Watch the Demo

When to Use COUNTAX vs. Other Functions

Choosing the right tool for the job is crucial for writing efficient and readable DAX. Here's a cheat sheet for when to use what.

• Use COUNTAX When: You need to count something that can only be determined after a row-by-row calculation or logical check. If your question contains phrases like "count how many X had a Y greater than Z," there's a good chance COUNTAX (often paired with FILTER) is your answer.
• Use COUNTROWS When: You simply need to count the number of rows in a table. It’s ideal for when your logic is entirely contained within a filter. For example, COUNTROWS(FILTER('SalesData', 'SalesData'[Region] = "West")) is cleaner than structuring a complex COUNTAX if all you need is a row count.
• Use DISTINCTCOUNT When: Your goal is to count the number of unique or distinct items in a column. For example, "How many different customers made a purchase?" is a classic DISTINCTCOUNT problem.

Final Thoughts

Mastering functions like COUNTAX moves you from a beginner Power BI user to someone who can truly shape data to answer complex business questions. It allows you to inject your own custom logic directly into your aggregation, an ability that is fundamental to building insightful and dynamic reports.

At the same time, working through DAX can be time-consuming, with a significant learning curve. You often spend more time trying to write the perfect formula than you do analyzing the results. We built Graphed for this very reason. It allows you to skip the complex DAX by simply connecting your data sources and asking questions in natural language. Instead of writing formulas, you can just ask, "Show me a chart of the number of high-value orders by month," and get an interactive dashboard built for you instantly.