COUNT(), COUNTROWS(), & DISTINCTCOUNT()

Posted by

September 21, 2026

On June 30, 2024

DAX (Data Analysis Expressions) functions are the backbone of Power BI’s powerful data manipulation capabilities. Among these, COUNT(), COUNTROWS() and DISTINCTCOUNT() are essential functions that every Power BI user should master. This blog delves into these functions, providing not only their basic usage but also advanced techniques to make your data analysis more exciting and insightful.

Download the Power BI file associated with this blog to see these DAX functions demonstrated in detail from below.

Introduction to DAX Functions

COUNT(), COUNTROWS() and DISTINCTCOUNT() are often highlighted in our Power BI training sessions. Understanding their distinct functionalities is crucial for leveraging the full power of DAX. Let’s start by exploring these functions and then move on to some advanced use cases that can transform your data analysis.

Getting Started with COUNT()

Before diving into COUNTROWS() and DISTINCTCOUNT(), let’s quickly review the COUNT() function. COUNT() is a fundamental DAX function that counts the number of non-blank values in a column.

COUNT() Function

Syntax:

Creating a Measure with COUNT()

Create a New Measure:

Right-click on Product Table in the Data Pane and select New Measure.
Enter the following formula:

This formula counts the non-blank values in the Product Code column from the Product Table.

Visualize the Data:

Select the Matrix visual in the Build Pane.
Drag and drop the Product Line from the Product Table to the rows.
Drag and drop Total Number of Products (CM) from Products Table to the Values.

Counting Rows with COUNTROWS()

COUNTROWS() is used to count the number of rows in a table, which is especially useful for understanding the volume of transactions or entries in your data.

Using the COUNTROWS() Function

Syntax

Create a New Measure:

Right-click on Sales Table in the Data Pane and select New Measure.
Enter the following formula:

This formula counts the number of rows in the Sales Table, representing the total number of transactions.

Visualize the Data:

Select the matrix visual we created in step 2.
Drag and drop Number of Sales (CM) from the Sales Table to the Values.

Finding Unique Values with DISTINCTCOUNT()

DISTINCTCOUNT() is perfect for counting unique values in a column, such as the number of distinct products sold.

Using the DISTINCTCOUNT() Function

Syntax

Create a New Measure:

Right-click on Sales Table in the Data Pane and select New Measure.
Enter the following formula:

This formula counts the unique Product Code in the Sales Table.

Visualize the Data:

Select the Card visual in the Build Pane.
Drag and drop Total Number of Products Sold (CM) from Sales Table to the Fields.

Detailed Breakdown:

Select the matrix visual we created in step 4.
Drag and drop Total Number of Products Sold (CM) from Sales Table to the Values.

Advanced Use Cases for DAX Functions

Now that we’ve covered the basics, let’s explore some advanced use cases that demonstrate the true power of COUNTROWS() and DISTINCTCOUNT().

Combining COUNTROWS() with FILTER()

You can use COUNTROWS() in combination with FILTER() to count rows that meet specific criteria. For example, to count the number of sales transactions where the quantity sold is greater than 10:

Syntax

Example

Visualize the measure in the Matrix visual.

Difference between COUNT(), DISTINCTCOUNT() and COUNTROWS():

COUNT() and DISTINCTCOUNT() are two functions commonly used in DAX. Both of these functions take a single column as an argument. Both of these functions are used for counting the values. However, COUNT() counts the duplicates but DISTINCCOUNT() does not count the duplicates. COUNT() excludes the blank values. COUNTROWS() is used to count the number of rows in a table.

Learnings from this Blog:

COUNT(), COUNTROWS(), and DISTINCTCOUNT() are fundamental DAX functions that play a crucial role in data analysis with Power BI. While each function serves a unique purpose, combining them with other DAX functions and techniques can unlock advanced analytical capabilities. Whether you’re counting transactions, analyzing unique values, or segmenting your data dynamically, mastering these functions will significantly enhance your data analysis skills.

Are you a data analyst eager to take your Power BI skills to the next level? Sign up for our Power BI training in Australia and explore our range of courses:

Elevate your data analytics game with our expert-led training programs and become a Power BI pro!

Downloads

FAQs

What is the difference between COUNT(), DISTINCTCOUNT(), and COUNTROWS()?

COUNT(): Counts non-blank values in a column, including duplicates.
DISTINCTCOUNT(): Counts unique values in a column, excluding duplicates.
COUNTROWS(): Counts the number of rows in a table.

Can COUNTROWS() and DISTINCTCOUNT() be used together?

Yes, you can combine COUNTROWS() and DISTINCTCOUNT() to perform more complex calculations. For example, you might use COUNTROWS() to count the number of rows in a filtered table and DISTINCTCOUNT() to count unique values within those rows.

How can I improve performance when using DISTINCTCOUNT()?

To improve performance, ensure that the column being counted has an appropriate data type and indexing. Additionally, consider using summary tables or pre-aggregating data when possible.