How to Count a Measure in Power BI

Trying to count rows based on the result of a measure in Power BI can feel like you're trying to solve a riddle. You can't just put a measure inside a standard COUNT function, because they operate on different levels. This article will show you how to solve this common problem using a powerful combination of DAX functions to count exactly what you need.

Graphed

Still Building Reports Manually?

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

Watch the Demo

Why Can't You Just "Count" a Measure Directly?

Before diving into the solution, it’s helpful to understand why this is a challenge in the first place. It comes down to the fundamental difference between a calculated column and a measure in Power BI.

• A calculated column is computed for each row in your table during a data refresh and is physically stored in your data model. It’s a static value. If you have a column for "Sales Amount," you can easily count how many rows have a value over $100 because each row already has that pre-calculated value.
• A measure is a dynamic calculation that runs at query time. Its result depends entirely on the context of your report - the filters, slicers, and visuals you're using. A measure doesn't exist as a neat column of values to be counted, it returns a single, aggregated value (a scalar) based on the context it's given.

Think of it this way: a calculated column is like a fixed price tag on every item in a store. A measure is like the answer to the question, "What's the total value of items in this customer's shopping cart right now?" The answer constantly changes as the customer adds or removes items. You can't "count" that changing total directly, you have to count the items after applying the rule about their total value.

To count something based on a measure, we need to tell Power BI to first calculate the measure for each item we want to count (like each order or customer), and then count how many of those items meet our criteria.

Graphed

Still Building Reports Manually?

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

Watch the Demo

Let's Use a Practical Example: Counting High-Value Orders

To make this concrete, let's imagine you run an e-commerce store. You have a Sales table that contains details about every line item sold, including OrderID, Product, Quantity, and Price.

First, you already have a simple measure to calculate the total value of any given order:

Total Order Value = SUMX('Sales', 'Sales'[Quantity] * 'Sales'[Price])

This measure works perfectly. If you put it in a table visual next to OrderID, it correctly shows the total value for each order. Now, you have a new business question: "How many individual orders have a total value greater than $100?"

This is where we hit the wall. You can’t just write COUNT([Total Order Value] > 100). We need a smarter DAX approach to answer this.

The Solution: Combining COUNTROWS, FILTER, and VALUES

The secret to solving this lies in creating a "virtual" table in your DAX formula. This virtual table is a temporary table that exists only for the duration of the calculation. We'll create a list of all unique order IDs, calculate our [Total Order Value] measure for each one, filter that list down to only the orders that are over $100, and then count the remaining rows. It sounds complex, but it boils down to one powerful formula.

Here’s the step-by-step process:

Step 1: Get a Unique List of Items to Count

First, we need Power BI to "see" a list of all the individual things we want to evaluate. In our case, that's each unique OrderID. The VALUES() function is perfect for this. VALUES('Sales'[OrderID]) generates a temporary, single-column table containing every unique order ID from our Sales table.

Graphed

Still Building Reports Manually?

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

Watch the Demo

Step 2: Filter the List Based on Your Measure's Result

Next, we need to filter this virtual table of order IDs. We only want to keep the ones where our [Total Order Value] measure is greater than $100. The FILTER() function lets us do this. FILTER() takes two arguments:

1. A table to iterate over (our VALUES('Sales'[OrderID]) from Step 1).
2. A condition to check for each row in that table.

So, our expression will look like this: FILTER(VALUES('Sales'[OrderID]), [Total Order Value] > 100). For each unique OrderID in our virtual list, Power BI calculates [Total Order Value] in that context and checks if it's over $100. If it is, the OrderID is kept, if not, it's discarded.

Important note: This works because functions like FILTER that iterate over a table create a "row context." This means as DAX looks at each OrderID row in the VALUES list, the [Total Order Value] measure is automatically calculated just for that specific order. This context transition is the key to making this whole technique work.

Step 3: Count the Rows in the Filtered List

The FILTER function returns a table. All we have to do now is count how many rows are in that resulting table. The COUNTROWS() function does exactly that. We simply wrap our FILTER logic from Step 2 inside COUNTROWS().

Putting It All Together

Here is the final DAX formula. You can create a new measure and paste this in:

Count of High Value Orders =

COUNTROWS(

&nbsp,&nbsp,&nbsp,&nbsp,FILTER(

&nbsp,&nbsp,&nbsp,&nbsp,&nbsp,&nbsp,&nbsp,&nbsp,VALUES('Sales'[OrderID]),

&nbsp,&nbsp,&nbsp,&nbsp,&nbsp,&nbsp,&nbsp,&nbsp, [Total Order Value] > 100

&nbsp,&nbsp,&nbsp,&nbsp,)

)

Now, when you add this [Count of High Value Orders] measure to a card visual or a report, it will correctly display the total count of individual orders that had a value over $100, dynamically adjusting to any filters (like date ranges or product categories) you apply to your report.

Graphed

Still Building Reports Manually?

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

Watch the Demo

An Alternative Approach Using SUMMARIZE

Another common way to achieve this is with the SUMMARIZE function. SUMMARIZE is a more powerful tool for creating virtual tables, as it can group data and create new columns within the virtual table itself. For this specific problem, it's a bit more verbose, but it's good to know.

Here’s what the formula would look like using SUMMARIZE:

Count of High Value Orders (SUMMARIZE) =

COUNTROWS(

&nbsp,&nbsp,&nbsp,&nbsp,FILTER(

&nbsp,&nbsp,&nbsp,&nbsp,&nbsp,&nbsp,&nbsp,&nbsp,SUMMARIZE(

&nbsp,&nbsp,&nbsp,&nbsp,&nbsp,&nbsp,&nbsp,&nbsp,&nbsp,&nbsp,&nbsp,&nbsp,'Sales',

&nbsp,&nbsp,&nbsp,&nbsp,&nbsp,&nbsp,&nbsp,&nbsp,&nbsp,&nbsp,&nbsp,&nbsp,'Sales'[OrderID]

&nbsp,&nbsp,&nbsp,&nbsp,&nbsp,&nbsp,&nbsp,&nbsp,),

&nbsp,&nbsp,&nbsp,&nbsp,&nbsp,&nbsp,&nbsp,&nbsp, [Total Order Value] > 100

&nbsp,&nbsp,&nbsp,&nbsp,)

)

In this version, SUMMARIZE('Sales', 'Sales'[OrderID]) is used to create the same unique list of order IDs. For this particular use case, VALUES is often slightly more performant and easier to read, but SUMMARIZE becomes essential when you need to group by multiple columns or perform more complex interim calculations.

Common Pitfalls and Best Practices

• Check Your Granularity: Make sure the column you use in VALUES() or SUMMARIZE() matches what you're trying to count. If you want to count customers, use VALUES('Customer'[CustomerID]). If you want to count products, use VALUES('Product'[ProductID]).
• Performance on Large Datasets: Be mindful that these "iterator" functions like FILTER can be computationally expensive on very large datasets (millions of rows). If your report is slow, a potential alternative is to create a calculated column for Total Order Value during the data refresh. This moves the workload from query time (CPU) to refresh time (RAM and refresh duration). But doing this as a measure is more flexible and is usually the recommended first approach.
• Blanks can be Tricky: If your measure can return a BLANK value, your filter condition might not behave as expected. You may need to handle blanks explicitly with an IF statement or by adding another condition like [Your Measure] <> BLANK().

Final Thoughts

Counting based on a measure might seem complicated, but it's a core DAX pattern that unlocks a much deeper level of analysis in Power BI. By creating a virtual table for your dimension (like orders or customers), using FILTER to apply your measure logic, and then wrapping it all in COUNTROWS, you can count almost anything based on complex, dynamic conditions.

We know wrestling with DAX functions like iterators and context transition can be a real headache, taking hours away from a task that should be simple. Instead of building these formulas and virtual tables by hand, we built Graphed to handle the heavy lifting. You can connect your data sources and simply ask in plain English, "Count how many orders were over $100 last month," and get an instant, accurate answer and visualization without writing a single line of DAX code.