COUNTROWS() and DISTINCTCOUNT() are amongst the most commonly used DAX functions so we cover these functions in our Power BI training every time. It is important to understand the working of each of these to use them correctly. In this blog, a brief introduction to COUNTROWS() and DISTINCTCOUNT() has been presented along with a use case of each of these.
Before we move further, lets quickly create a measure using COUNT() first.
COUNT():
To use COUNT() function:
- Right click on All Measures in the Fields Pan.
This table is created to store all the measures separately. Measures do not belong to any table, so it is a best practice to create a separate table for them.
- Click on New measure.
Measure tools open.
- Type the following formula in the formula bar and press Enter.
In this Expression:
- The non-blank values in the Product Key column from in Product table are counted.
- Click on the matrix visual in the visualization pane.
- Drag and drop Category from the Products table to the rows.
- Drag and drop Total Number of Products (CM) from All Measures table to the Values.
Notice that we have a total of 397 products with the highest number of products in the Components category.
Counting Rows
Now let’s learn how to count rows in any table using DAX function COUNTROWS().
COUNTROWS():
To use COUNTROWS() function:
- Right click on All Measures in the Fields.
- Click on New measure.
Measure tools open.
- Type the following formula in the formula bar and press Enter.
In this Expression:
- The number of rows in the Sales Table are counted. This also corresponds to the total number of transactions.
- Click on the matrix visual in the visualization pane.
- Drag and drop Category and Subcategory from the Products table to the rows.
- Drag and drop Total Number of Products (CM), Number of Sales (CM) from All Measures table to the Values.
Notice that although the category of Accessories has 35 products only, the greatest number of sales are recorded for this category.
Use of DISTINCTCOUNT ()
Now, you will learn how to find the distinct products sold using DAX functions.
To use DISTINCTCOUNT() function:
- Right click on All Measures in the Fields.
- Click on New measure.
Measure tools open.
- Type the following formula in the formula bar and press Enter.
In this Expression:
- The number of unique product keys in the Sales table are counted.
- Click on the card visual in the Visualization Pane.
- Drag and drop Total number of Products Sold (CM) from All Measures to the Fields.
Notice that 350 out of 397 distinct products have been sold.
- Click on the matrix visual in the visualization pane.
- Drag and drop Category and Subcategory from the Products table to the rows.
- Drag and drop Total Number of Products (CM), Number of Sales (CM) and Total Number of Products Sold (CM) from All Measures table to the Values.
Notice the breakdown of values due to the incoming filter context. Notice that all the products from the category of bikes have been sold. Whereas only 150 products out of 189 total products in the category of components have been sold.
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.
Conclusion:
COUNT(), COUNTROWS() and DISTINCTCOUNT() are commonly used for counting values. Although the basic function for all these is counting values but each of these gives a different output based on its functionality. This blog introduces these three functions with their use cases and presents the key difference between each of them.
Are you a data analyst and want to learn more about Power BI? Why not sign up for Power BI training in Australia. We provide the following Power BI trainings:
