How to Count Non-Blank Rows in Power BI

Trying to count rows in Power BI that aren't empty sounds simple, but it can be surprisingly tricky depending on what you're trying to achieve. Whether you're assessing data quality, calculating completion rates, or just trying to build a clean report, getting an accurate count of non-blank rows is a fundamental skill. This guide will walk you through the most effective and common ways to do this using DAX, from the simple and straightforward to the more flexible and powerful methods.

Graphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Build Your First Agent

Why Counting Non-Blank Rows Matters

Before diving into the "how," let's quickly touch on the "why." You'll find yourself needing this calculation in countless business scenarios. It's often one of the first steps in understanding the completeness and reliability of your dataset.

Common use cases include:

• Data Quality Checks: Quickly identify how many records are missing critical information, such as email addresses, phone numbers, or shipping details. A high number of blanks might signal a problem with your data collection process.
• Lead & Sales Funnel Analysis: Calculate the number of leads that have an assigned owner or a follow-up date noted, helping you see where gaps exist in your pipeline.
• Survey Completion Rates: If you collect survey data, you can count how many respondents answered a specific optional question to gauge engagement or question clarity.
• Performance Reviews: Determine the percentage of sales transactions that have a customer satisfaction score attached or the number of projects with a completed end date.

In short, counting non-blanks is your first line of defense against incomplete data corrupting your analysis. It transforms raw data into a reliable foundation for making decisions.

Understanding the Key DAX Functions

Power BI’s formula language, DAX (Data Analysis Expressions), gives us several tools to tackle this task. The one you choose will depend on the complexity of your needs. Here are the core functions you'll be working with:

• COUNTA(): This is your go-to function for simple counts. It counts every cell in a column that is not empty. It works with text, numbers, and dates, making it very versatile.
• COUNTROWS(): As the name suggests, this function counts the total number of rows in a table. By itself, it’s not useful for our specific goal, but when paired with the FILTER function, it becomes incredibly powerful.
• FILTER(): This is an iterator function that creates a temporary table containing only the rows that meet a specific condition you define. This is the key to creating sophisticated, conditional counts.
• ISBLANK(): A simple check that returns TRUE if a value is blank and FALSE if it is not. We'll often combine this with the NOT() function.
• NOT(): A logical function that reverses the value of its argument. So, NOT(ISBLANK()) becomes your way of asking, "Is this cell not blank?"

Now, let's put these functions to work in some real-world examples.

Method 1: The Quick and Easy Way with COUNTA

For most simple scenarios where you just need to count the non-empty cells in a single column, COUNTA is your best friend. It’s fuss-free, easy to remember, and gets the job done quickly.

Let's imagine you have a 'Customers' table and you want to know how many of them have an email address listed in the 'Email' column.

AI Agents for Marketing

Crash Course

Graphed

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Download the PDF

Step-by-Step Instructions:

1. On the right-hand side of Power BI, in the Fields pane, right-click on the table where you want to add the calculation (in our case, the 'Customers' table).
2. Select New measure from the context menu. This will open the formula bar at the top.
3. Type the following DAX formula into the bar and press Enter:

That's it! You've just created a new measure named "Number of Customers with Email." You can now drag this measure into a Card visual to see the total count, or use it in a table or matrix to add context to other dimensions.

When to use COUNTA: Use this method when your question is simple: "How many non-blank values are in this specific column?" It's perfect for quick checks and high-level dashboard KPIs.

Method 2: The Flexible Approach with COUNTROWS and FILTER

While COUNTA is great, it has its limits. What if you need to apply more conditions to your count? This is where the combination of COUNTROWS and FILTER truly shines. This pattern is one of the most important to learn in DAX because it unlocks a huge range of reporting possibilities.

Let's stick with our 'Customers' table, but this time, let's count how many customers have a value in the 'PhoneNumber' column. While you could use COUNTA, we'll use this new method to demonstrate its flexibility.

The logic here is to first filter the table to only include rows where the phone number is not blank, and then count how many rows are left.

Step-by-Step Instructions:

1. Create another New measure in your 'Customers' table.
2. Enter this more detailed DAX formula:

Breaking Down the Formula:

• FILTER(Customers, ...): This function is creating a virtual, temporary copy of your 'Customers' table.
• NOT(ISBLANK(Customers[PhoneNumber])): This is the condition for the filter. For each row in the 'Customers' table, it checks if the 'PhoneNumber' field is blank. The NOT() function flips the result, so the filter only keeps the rows where the phone number is not blank.
• COUNTROWS(...): This function wraps around the whole expression and simply counts the number of rows in the filtered, temporary table that was generated.

When to use COUNTROWS(FILTER(...)): While it's slightly more complex to write, this approach is far more powerful. Use it as your default when you anticipate needing to add other conditions later. It's the scalable solution.

Graphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Build Your First Agent

Taking it Further: Advanced Conditional Counts

The real beauty of the COUNTROWS(FILTER(...)) pattern is how easily you can add more conditions to answer more complex business questions.

Scenario 1: Counting Rows Where Multiple Columns are Not Blank

Imagine you want to count "Fully Qualified Leads" — customers who have both an email address and a phone number. This requires checking two columns at once.

To do this, we can easily extend our FILTER condition using the double ampersand (&&), which works as an "AND" operator in DAX.

DAX Formula:

Fully Qualified Leads =

COUNTROWS(

FILTER(

Customers,

NOT(ISBLANK(Customers[Email])) && NOT(ISBLANK(Customers[PhoneNumber]))

)

)

Here, the FILTER function now only keeps rows where the 'Email' column is not blank and the 'PhoneNumber' column is not blank. COUNTROWS then gives you the final count of these truly complete records.

Scenario 2: Combining a Non-Blank Check with Another Business Rule

Let's get even more specific. How many of your active VIP customers have provided an email address? This requires checking for a non-blank value plus another condition from a different column (e.g., a 'CustomerType' column).

DAX Formula:

VIPs with Email =

COUNTROWS(

FILTER(

Customers,

Customers[CustomerType] = "VIP" && NOT(ISBLANK(Customers[Email]))

)

)

This measure filters the 'Customers' table down to only the rows where the 'CustomerType' is "VIP" and where the 'Email' field has a value. This kind of targeted, multi-conditional analysis is what makes Power BI such a powerful tool for businesses.

AI Agents for Marketing

Crash Course

Graphed

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Download the PDF

Alternative: Handling Blanks in Power Query

DAX is fantastic for performing calculations on your data model, but sometimes the best approach is to clean your data before it even gets loaded. This is done in the Power Query Editor, the data transformation layer of Power BI.

Instead of creating measures to count around blank rows, you can choose to remove them entirely during the data import process.

When to Consider Power Query:

If a row is completely useless without a value in a specific column (e.g., a sales record with no sale amount), you might as well filter it out from the start. This makes your data model cleaner, smaller, and faster.

How to Do It:

1. In Power BI Desktop, click on Transform data to open the Power Query Editor.
2. Select the column where you want to remove blanks.
3. Click the filter drop-down arrow on the column header.
4. Uncheck the box next to （Blank） and null and click OK. Alternatively, you can choose the "Remove Empty" option.

Heads Up: Be cautious with this approach. When you remove rows in Power Query, that data is gone from your report for good. If you need to analyze why some rows are blank, you should leave them in and use the DAX methods discussed above instead.

Final Thoughts

Mastering how to count non-blank rows is a key step in moving from a basic Power BI user to a proficient analyst. For quick and simple counts of a single column, COUNTA is the perfect tool. For more robust, flexible, and conditional counts that can adapt to changing business questions, the COUNTROWS(FILTER(...)) pattern is the gold standard you should practice and rely on.

While mastering DAX can be incredibly empowering, it often feels like you're wrestling with formulas just to answer a straightforward question. Sometimes, you just want to know "how many active customers have an email" without having to stop and write code. This is exactly why we built Graphed. After connecting your data sources, we let you create reports and ask questions in plain English. No need to look up DAX syntax – you just ask, and our AI builds the report for you in seconds, giving you back time to focus on strategy instead of formulas.