Power BI Count If True: COUNTIF Measures With CALCULATE and COUNTROWS

If you need a Power BI count if true, like the number of orders over $5,000 for each sales rep, don’t look for a COUNTIF function. DAX doesn’t have one. You write a measure: COUNTROWS inside CALCULATE, with the true condition as the filter.

Most people get stuck because they drag a True/False column into a visual and pick Count. That counts every row, TRUE and FALSE. On my 60 sample orders it returned 60 instead of 12.

In this tutorial, we’ll build a power bi count if true measure three ways, show 0 instead of blank, count true rows from another table and turn the count into a percentage. Every number below comes from a real Power BI Desktop report.

The Sample Sales Data We’ll Use

My demo files are two CSVs loaded with Home > Get data > Text/CSV. The Sales table has 60 orders from 2026 with OrderID, OrderDate, Region, City, State, ZIP, Category, Product, SalesRep, Quantity and Sales (in USD).

The Sales Reps table has one row per rep with SalesRep, Email and Title. Power BI created the relationship Sales[SalesRep] many-to-one Sales Reps[SalesRep] by itself. If your tables don’t link that easily, see creating a relationship without unique values.

Our condition is simple: an order is a large order when Sales is $5,000 or more. Here’s what the counts should be, so you can check your own report:

SalesRep	Title	Orders	Orders of $5,000+
Dana Whitfield	Sales Manager	11	0
Tyler Brooks	Sales Rep	9	0
Brian Keller	Sales Rep	11	5
Rachel Morgan	Sales Rep	12	5
Marcus Hale	Sales Rep	17	2
Total		60	12

By region, East has 32 orders (7 large), West 15 (5 large), South 10 and Central 3 (none large). All 12 large orders are Laptops.

Two rows are tricky on purpose: Dana Whitfield and Tyler Brooks have no large orders at all. They’re the reason the blank-versus-0 section exists.

Step 1: Create a True/False Column

You don’t need a helper column for a count (Method 3 shows why), but most “count if true” questions start with one. Go to Table view, select the Sales table, then choose Table tools > New column and enter:

Is Large Order = Sales[Sales] >= 5000

Because the formula is a comparison, Power BI sets the Data type to True/false on its own. The column shows True for the 12 large orders and False for the other 48.

Notice the Data type box: you didn’t pick True/false, the comparison did. If a flag column from your source loads as text (“TRUE” in quotes), compare it to the text instead, or change its type in Power Query. For more on DAX comparisons, see the Power BI IF statement guide.

Method 1: Count If True With CALCULATE and COUNTROWS (Recommended)

This is the one I use almost every time. It works in any visual, respects slicers, and reads like the question you’re asking.

Same Sales table as above. On the Home ribbon, select New measure and enter this:

Large Orders =
CALCULATE(
    COUNTROWS(Sales),
    Sales[Is Large Order] = TRUE()
)

Put Sales Reps[SalesRep] and the measure in a Table visual. Brian Keller and Rachel Morgan show 5, Marcus Hale shows 2, and the total is 12.

Look at the circled total of 12, and at the empty cells for Dana Whitfield and Tyler Brooks. Those reps aren’t 0, they’re blank. We fix that in a later section.

How does this work?

COUNTROWS(Sales) counts rows in whatever filter context it gets. On its own, in Brian Keller’s row, it returns 11 because the relationship filters Sales down to his orders.

CALCULATE adds one more filter: Sales[Is Large Order] = TRUE(). Now only his 5 large orders are left, so the measure returns 5. In the total row, the rep filter is gone and all 12 large orders count.

For a wider tour of counting functions (COUNT, COUNTA, COUNTBLANK, DISTINCTCOUNT), read our Power BI DAX Count examples.

Method 2: Count If True With COUNTROWS and FILTER

You’ll see this version in a lot of forum answers. It gives the same result, so it’s worth knowing how to read it.

Same Sales table and the same report page. Create another measure:

Large Orders FILTER =
COUNTROWS(
    FILTER(Sales, Sales[Is Large Order])
)

It returns 5, 5 and 2 for the three reps with large orders and 12 in total, exactly like Method 1.

Notice that the filter condition is just the column name, with no = TRUE(). A True/false column already is the condition.

How does this work?

FILTER walks through every row of Sales in the current context and keeps the rows where Is Large Order is True. It returns a table, and COUNTROWS counts that table.

Important: Microsoft recommends Boolean filters in CALCULATE (Method 1) over FILTER whenever possible, because they filter the column directly instead of scanning rows. Keep FILTER for conditions that need a measure or compare two columns. Our Power BI DAX FILTER guide covers those cases.

Method 3: Count Rows If a Condition Is True (No Helper Column)

Most people miss this: you don’t need the True/false column at all. CALCULATE can take the condition directly.

Same 60 orders, now counting Sales of $5,000 or more without Is Large Order:

Orders Over 5000 = CALCULATE(COUNTROWS(Sales), Sales[Sales] >= 5000)

The Orders Over 5000 column matches Large Orders on every row: 5, 5, 2 and a total of 12.

The circled 12 under Orders Over 5000 proves the point: same answer, one less column in the model.

How does this work?

Sales[Sales] >= 5000 is a Boolean filter on a single column, which CALCULATE accepts directly. Power BI keeps only those rows, and COUNTROWS counts them.

The “countif greater than 0” question works the same way. This measure returned 60 on my data, because every order has a positive amount:

Orders Above Zero = CALCULATE(COUNTROWS(Sales), Sales[Sales] > 0)

For two conditions, add a second filter argument. CALCULATE combines them with AND. With Sales of $5,000 or more in the East region, the result is 7, which matches the East count above:

Large East Orders = CALCULATE(COUNTROWS(Sales), Sales[Sales] >= 5000, Sales[Region] = "East")

For OR logic or more complex combinations, see counting with multiple filter conditions in Power BI and Power BI measures with multiple IF conditions.

Pro Tip: When the condition is fixed and you only need it in one measure, skip the helper column. Add the column when people also want to slice or filter by it.

Show 0 Instead of Blank for Rows With No Matches

Back to Dana Whitfield and Tyler Brooks. They’re blank in Methods 1 to 3 because COUNTROWS returns BLANK when no rows are left, not 0.

Same measure, with + 0 at the end:

Large Orders (show 0) =
CALCULATE(COUNTROWS(Sales), Sales[Is Large Order] = TRUE()) + 0

Now both reps show 0 in the Large Orders (show 0) column, and the other values don’t change.

Look at the two boxed zeros. The values for Brian, Rachel and Marcus are untouched.

How does this work?

BLANK + 0 returns 0 in DAX, and 5 + 0 is still 5. Only use it when a 0 really means something to the reader. More options are in Power BI IF NULL then 0.

Turn the Count Into a Percentage of True Rows

Once you have the count, a percentage is one more measure. Same report, still the Large Orders measure from Method 1:

% Large Orders = DIVIDE([Large Orders], COUNTROWS(Sales))

Set the format to Percentage on the Measure tools ribbon. Brian Keller shows 45.45% (5 of 11 orders), Rachel Morgan 41.67% (5 of 12), Marcus Hale 11.76% (2 of 17), and the total is 20.00% (12 of 60).

Dana and Tyler stay empty here because their Large Orders value is blank, and DIVIDE passes that blank through. For other percentage patterns, see calculating the percentage of two columns in Power BI.

Why Count of a True/False Column Gives the Wrong Number

This is the mistake from the intro. When you add Is Large Order to a table, it defaults to Don’t summarize and splits rows into True and False groups. Open the field menu and the Summarization list offers only Don’t summarize, Count (Distinct) and Count.

Same Sales table, with Region in the rows and Count picked for Is Large Order:

Count of Is Large Order returns 32 for East and 60 in total, while the Large Orders measure shows 7 and 12. Count counts every row that has a value, and False is a value.

Count (Distinct) isn’t the answer either: it counts how many different values exist (True and False), not how many rows are True. That’s the same idea behind a Power BI distinct count with a filter. For a true count, always use a measure.

Method 4: Count If True From Another Table

Sometimes the count belongs in a different table, like a column on Sales Reps that says how many large orders each rep has. That’s the “countif from another table” question.

Same two tables. Go to Table view, select Sales Reps, choose New column and add the total order count first:

Total Orders = COUNTROWS(RELATEDTABLE(Sales))

That returns 11, 9, 11, 12 and 17. Then add the count of true rows:

Large Order Count =
CALCULATE(
    COUNTROWS(Sales),
    Sales[Is Large Order] = TRUE()
) + 0

Large Order Count shows 0 for Dana Whitfield and Tyler Brooks, 5 for Brian Keller and Rachel Morgan, and 2 for Marcus Hale.

Notice the step numbers: New column first, then the formula. The boxed column is stored in the Sales Reps table, so you can sort or filter by it like any other field.

How does this work?

A calculated column has a row context, not a filter context. CALCULATE turns the current Sales Reps row into a filter (Microsoft calls this context transition), and that filter flows through the relationship to Sales.

RELATEDTABLE does the same thing in one word: it’s a shortcut for CALCULATETABLE with no extra filter. Both only work because the relationship is active, so check the cross filter direction if the numbers come out the same on every row.

Pro Tip: Prefer a measure (Method 1) when the count must change with slicers. A calculated column is computed at refresh and ignores report filters.

Which Method Should You Use?

You want to …	Use
Count true rows in any visual, with slicers	Method 1: CALCULATE + COUNTROWS
Read or reuse a forum formula	Method 2: COUNTROWS(FILTER())
Count a condition without a helper column	Method 3: CALCULATE with the condition
Show 0 for people or groups with no matches	Any measure + 0
Store the count in a related table	Method 4: calculated column with CALCULATE

If you’re unsure, go with Method 1. Once you have the count, you can color it with conditional formatting based on a measure, so reps with no large orders stand out. More DAX patterns are in our Power BI tutorials.

Things to Keep in Mind

• Blank instead of 0: COUNTROWS returns BLANK when no rows match. Add + 0 only where a 0 is useful.
• Count shows every row: the Count summarization counts TRUE and FALSE values. Use a measure instead.
• Flag column is text: if TRUE comes in as text, compare to “TRUE” or change the data type to True/false in Power Query.
• Calculated column ignores slicers: it’s computed at refresh. Use a measure for counts that must follow filters.
• Same count on every row from another table: the relationship is missing or inactive. Check Model view first.

These guides build on the same sales data and DAX ideas:

• Power BI Percentage of Total by Category [With Real Examples]
• Power BI Month over Month Change [With Examples]
• Power BI Group by | Power BI Count Group by
• Power BI Measure If Text [With Examples]

Frequently Asked Questions