Sign in or start a free trial to avail of this feature.
10. Count Functions
There are two major count functions in DAX: Count and Distinctcount. In this lesson, we will see both and learn the difference between the two..
Count functions in DAX
DAX has 2 count functions:
- Count simply counts the number of rows where there is a customer ID for the product in question
- This means it actually counts the number of transactions for each product
- Distinctcount disregards duplicate customer IDs, so it counts the number of individual customers that each product has
- Alt N, V – Create Pivot Table (from Excel window)
- Alt B, F Manage Measures
- Alt H, P – Format as p0ercentage
In a previous lesson, we used a distinct-count function to identify the number of customers for each pharmaceutical product. In this lesson, we'll take a look at the count and distinct-count functions in more detail.
There are two major count functions in DAX, count and distinct-count. In order to understand the difference between these two functions, we'll count the number of customers for each product.
To do this, we'll create a measure called Number of Customers.
It will be equal to the count function of the Customer ID field from the Pharma Data table.
We'll now create a new pivot table using the data model.
We'll add the Product Name field, and the Number of Customers measure, right-click any entry in the Number of Customers column, and sort from largest to smallest.
This suggests that Lumesta has the most customers with 8,736.
However, the dataset contains only 122 customer IDs, so this can't be accurate.
The count function is actually counting the number of customer IDs associated with each product sale, including any duplicate entries that appear when a customer buys a product multiple times.
In effect, this pivot table is counting the number of transactions for each product.
What we actually want to graph is a number of unique customers who bought each product. To do this, we need the distinct-count function.
We'll navigate to Manage Measures, select the Number of Customers measure, and change the formula from count to distinct-count.
We'll then press OK and close the window to see that the numbers in the table have changed.
Now, Polesta has the highest number of customers with 110. This is more in line with our expectations.
Distinct-count counts the number of unique customer IDs for each product, ignoring all duplicates.
We can also use the count functions to see what percentage of all the customers in the dataset bought each product.
This will be represented by the total number of distinct customers for any specific product, divided by the total number of customers for all products.
We'll create a new measure for this calculation and call it Percentage of Customers.
The numerator will be a distinct-count of customer ID.
To get the denominator, we'll enter a calculate function, and calculate a distinct count of customer IDs for all products. Similar to previous lessons, we used the ALL function to ensure there are no filters on the Product field when calculating the denominator.
We'll add the Percentage of Customers measure to the pivot table, re-sort the number of customers from largest to smallest, then select the new column, and format as a percentage with the shortcut Alt-H-P.
The table now tells us the percentage of customers that bought each product at least once.
For example, 90% of the customers in the dataset bought Polesta, and 88% bought Melacaine.
This concludes our look at the count functions index.
If you're familiar with pivot tables, you may know that we did not have to use DAX formulas to create this table.
Instead, we could've used the value field settings to produce a Percentage of Customers column without using DAX.
However, familiarity with these functions allows you to use them in more complex formulas, such as those with the calculate function.
In the final two lessons of this course, we'll look at how you can use DAX to manipulate text strings.