Sign in or start a free trial to avail of this feature.
10. Count and Distinctcount
Two of the major count functions in DAX are Count and DistinctCount. In this lesson, we will see both and learn the difference between the two.
Lesson Goal (00:17)
The goal of this lesson is to count our company’s customers and explore the differences between COUNT and DISTINCTCOUNT
Using the Count Function (00:26)
COUNT and DISTINCTCOUNT are the most common counting functions in DAX. Both take a single argument, which is a column reference. They return a count of all the elements in the column. The difference between the two is that COUNT counts duplicates while DISTINCTCOUNT does not.
As an example, we use COUNT to create a measure that counts the number of customers for each product. However, when we visualize this measure, the number of customers for each product is too high. In reality, COUNT counts the number of transactions where there is a customer ID. If a customer purchases a product multiple times, they are counted multiple times.
Using the Distinctcount Function (01:23)
The DISTINCTCOUNT function is similar to COUNT, except that it does not count duplicates. In our example, we change our measure to use DISTINCTCOUNT, and it calculates the total number of customers for each product correctly.
Counting Customers as a Percentage of Total (02:05)
We can use DISTINCTCOUNT to identify the percentage of customers in the data set that purchased each product. To do this, we create a measure that divides the number of customers for a product by the number of customers for all products.
The numerator of this measure uses DISTINCTCOUNT to count customer IDs. The denominator uses CALCULATE to calculate the same distinctcount, but uses the ALL function to filter the product names. After creating this measure, we use the Modeling tab to format it as a percentage, then add it to a visualization.
The visualization shows the percentage of customers in the data set who bought each product. Although this chart could be replicated without DAX, understanding the COUNT functions allows you to use them in more complicated formulas.
There are major counting functions available in DAX, Count and Distinctcount.
In a previous lesson, we used the Distinctcount function to identify the number of customers for each pharmaceutical product. In this lesson, we'll use both the Count and Distinctcount functions to count our company's customers and explore the differences between the two functions.
We'll start by creating a new measure called "Number of Customers." In this case, we'll simply enter the count function and evaluate the customer ID field.
Let's now create a stacked bar chart of number of customers by product name.
We'll put this chart on the left half of the canvas.
This chart suggests that Lunesta has the most customers, with 8,736.
If we navigate to data view and select the product details table, we can see that Lunesta only has 103 customers.
What accounts for this discrepancy? The count function is actually counting the number of transactions for each product, ignoring the fact that many customers will have multiple transactions for that same product.
What we actually want is to graph the number of unique customers for each product.
To do this, we need the Distinctcount function.
We'll select the "Number of Customers" measure from the fields list, and change the formula from Count to Distinctcount.
We'll then press enter and see that the scale of the graph has changed.
If we hover the mouse over Lunesta, we can see it now shows 103 customers, as we would expect.
Distinctcount counts the number of unique customer IDs for each product, ignoring all duplicates.
Let's take this further and use these count functions to see the percentage of customers in our data set that bought each product.
This would represented by the total number of distinct customers for a specific product divided by the total number of distinct customers for all products.
To that end, we'll create a new measure called "Percentage of Customers." The numerator will be a Distinctcount of customer ID.
To get the denominator, we'll use Calculate and calculate a Distinctcount of customer IDs with the filter "all" applied to the product name column.
In this case, the "all" function makes sure that the denominator counts the customers for all products in the data set.
Let's now copy our existing chart and paste it to the right half of the canvas.
We'll remove the number of customers from the values well in the new copy and add percentage of customers.
Next, we'll format the chart to show percentages.
We'll select the Percentage of Customers measure in the fields list, navigate to the modeling tab, and select the percentage icon to format the field.
The X-axis now shows data as a percentage.
If we sort the new chart by percentage of customers, we can see this is the same graph expressed slightly differently.
This chart is the equivalent to showing the values in the previous chart as a percentage of the total.
This graph tells us that 90% of customers in the data set bought Polesta at least once, 88% bought Melacaine, and so on.
You may have noticed that we did not actually need to use DAX formulas to create the graphs in this example.
In a previous course, we saw that Power BI allows you to aggregate data by a Count or Distinctcount, or even show values as a percentage of total directly in a visualization.
However, familiarity with these functions is essential as you'll be able to use them in more complex formulas, such as Calculate.
In the next two lessons, we'll tackle some of the text functions available in DAX.