How to Count Rows in Power BI

Counting rows is one of the most fundamental tasks in data analysis, whether you’re counting sales orders, support tickets, or website visitors. In Power BI, you can accomplish this in several ways, each suited for different situations. This article will show you the most effective methods for counting rows using both DAX formulas and the Power Query Editor.

Graphed

Still Building Reports Manually?

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

Watch the Demo

Why Does Counting Rows Matter?

Before jumping into the "how," it's helpful to understand the "why." Counting rows is the starting point for countless key performance indicators (KPIs) and business questions. You might need to know:

• The total number of orders placed in a given month.
• How many leads your sales team generated last quarter.
• The number of products currently in your inventory.
• The volume of customer service inquiries received each day.

In all these cases, you are fundamentally counting rows in a table. A "lead" is a row in your CRM data, an "order" is a row in your sales data, and so on. Mastering row counting is the first step toward building insightful reports that track your business's pulse.

The Two Main DAX Functions for Counting

Data Analysis Expressions (DAX) is the formula language used in Power BI. For counting, there are two primary functions you'll use constantly: COUNTROWS and COUNT. While they sound similar, they serve distinct purposes.

Graphed

Still Building Reports Manually?

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

Watch the Demo

Go-To Method: Using COUNTROWS to Count All Rows in a Table

The COUNTROWS function does exactly what its name implies: it counts the total number of rows in a table. It's simple, direct, and highly optimized for performance, making it the best choice for generic row-counting tasks.

The syntax is straightforward:

COUNTROWS( <Table> )

Step-by-Step Example: Counting Total Sales Transactions

Let's say you have a table named Sales where each row represents a single transaction. To create a measure that shows the total number of transactions, follow these steps:

1. From the Report View in Power BI, right-click on your Sales table in the Data pane on the right.
2. Select New measure from the context menu.
3. The formula bar will appear at the top of the canvas. Enter the following DAX formula:

Total Transactions = COUNTROWS(Sales)

1. Press Enter to save the measure.

That's it! You've just created a dynamic measure called Total Transactions. You can now drag this measure onto a Card visual to see the total count, or use it in a table visual broken down by date or product category. The best part is that this measure will automatically recalculate based on any filters you apply to your report, like a date slicer or a regional filter.

The Alternative: Using COUNT to Count Values in a Column

The COUNT function is slightly different. Instead of counting all rows in a table, it counts the number of non-blank cells in a specific column. It works with columns that contain numbers, dates, or strings, but it will ignore any blank or empty cells.

The syntax for COUNT is:

COUNT( <Column> )

When Would You Use COUNT?

You can use COUNT to get a total row count if you pick a column that you know will never have blank values, like a unique ID column (OrderID, CustomerID, etc.). In this scenario, the result would be identical to COUNTROWS.

For example, using our Sales table, this formula would likely give the same result as our previous measure:

Transaction Count (from Column) = COUNT(Sales[OrderID])

However, COUNT is truly useful when you specifically want to count entries in a column that might have blanks. For instance, if you want to count how many customers provided a phone number, you would use:

Customers with Phone Number = COUNT(Customers[PhoneNumber])

In this case, COUNTROWS(Customers) would give you the total number of customers, while the COUNT formula gives you only the subset who have a phone number on file.

Graphed

Still Building Reports Manually?

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

Watch the Demo

COUNTROWS vs. COUNT: A Simple Rule of Thumb

• Use COUNTROWS when you want to count the total number of rows in a table. This is your default choice for straightforward row counts like "total orders" or "total leads."
• Use COUNT when you want to count the non-empty values in a specific column. This is for more specific questions like "how many orders have a discount code applied?"

Counting Rows in Power Query for Data Preparation

Sometimes, you want to count rows before your data is even loaded into the Power BI model. This is done in the Power Query Editor, the data transformation engine in Power BI. This approach is excellent for data validation, cleaning, or when you need to pre-aggregate your data to improve report performance.

Method 1: The 'Count Rows' Transformation

This is a quick way to get a single number representing the total rows in your query. It's often used for a quick check or validation step.

1. Open the Power Query Editor by clicking Transform data on the Home ribbon.
2. Select the query (table) you want to count from the left-hand pane.
3. Go to the Transform tab in the ribbon.
4. Click Count Rows in the Table section.

Power Query will replace your entire table with a single numerical value - the total row count. This is useful for auditing data but be aware that it removes the underlying data from that specific query. It's usually a temporary step you would remove after checking.

Method 2: The 'Group By' Transformation

A more practical approach in Power Query is using the Group By feature to count rows for specific categories. This is extremely powerful for summarizing large datasets.

Imagine you have a Sales table and want to find the number of sales per Product Category.

1. In the Power Query Editor, with your Sales table selected, click Group By on the Home tab.
2. In the Group By dialog box:
3. Click OK.

Power Query will transform your data, leaving you with a summarized table showing each product category and its corresponding row count. This reduces the size of your data model and can make your reports much faster.

Graphed

Still Building Reports Manually?

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

Watch the Demo

Putting It All Together: Visualizing Your Row Count

Once you've created a DAX measure for your count, using it in your report is simple.

Showing a KPI with a Card Visual

A Card visual is perfect for displaying a single important number. Just drag your measure (e.g., Total Transactions) onto the report canvas and choose the Card visual from the Visualizations pane. Power BI will display your total row count as a prominent KPI.

Analyzing Trends in a Table or Bar Chart

To see how your count changes across different categories, use a Table, Matrix, or Bar Chart.

• Add a Bar Chart to your report.
• Drag a date field (like OrderDate) to the X-axis.
• Drag your Total Transactions measure to the Y-axis.

Power BI will instantly generate a chart showing the volume of transactions over time, helping you spot trends, seasonality, and outliers.

Final Thoughts

In summary, counting rows in Power BI is a core skill you can tackle with multiple tools. Use the DAX function COUNTROWS for the most direct and efficient way to count all rows as a measure in your reports. Use COUNT when you need to specifically count non-blank entries in a column. For data preparation and aggregation before your data is loaded, Power Query’s "Group By" transformation is incredibly powerful.

We know that learning DAX and navigating analytics tools can be a steep climb. The process of connecting data, writing formulas, and designing reports often gets in the way of finding quick answers. It's why we built Graphed. We wanted to eliminate that complexity. Instead of creating measures manually, you can simply ask questions in plain English like, "show me the total number of sales transactions last month," and get an instant visualization, allowing you to focus on the insights, not the setup.