In our previous article, we discussed “DATE DIMENSION Tables” in Power BI Dax. In this article, I would like to talk about the COUNT, DISTINCTCOUNT, and DISTINCTCOUNTNOBLANK functions, which are Power BI DAX Aggregation Functions
Let’s explain the differences between the COUNT(<column>), DISTINCTCOUNT(<column>), and DISTINCTCOUNTNOBLANK (<column>) functions, which are Dax aggregation functions, using the following simple example table. In this example table, I want to count the number of “Ahmet” names in the name column using these functions.
COUNT(<column>)
In Power BI DAX, the COUNT function is used to count the number of non-blank values in a column. For example, consider the table above. In this example, if we create our formula as a measure as follows, our output will be 3.
Why is the output=3 ?
It counted the names without making them unique and excluding blank records.
COUNT = COUNT(Sample_Customer[Name])
If we performed this calculation on the IDs as follows, the output would be 5. Because all the IDs are unique values, the total is 5.
DISTINCTCOUNT(<column>)
The DISTINCTCOUNT function counts all the unique values in a column, including blank values. For example, consider the table above. In this example, if we create our formula as a measure as follows, our output will be 2.
Why is the output= 2 ?
It counted the values (“Ahmet” and blank rows) found in our Sample_Customer[Name] column uniquely. It took 1 for the “Ahmet” name found in 3 rows and 1 for the 2 blank rows, giving an output of 2.
DISTINCTCOUNTNOBLANK (<column>)
The DISTINCTCOUNTNOBLANK function counts all the unique values in a column, but does not count blank values. It differs from the DISTINCTCOUNT function in that it does not take into account blank values. According to the following formula, the output is 1.
Why is the output=1 ?
It counted the values (“Ahmet”) found in our Sample_Customer[Name] column uniquely. Because it does not take into account blank values, the output is 1.
Summary:
| Feature | COUNT | DISTINCTCOUNT | DISTINCTCOUNTNOBLANK |
|---|---|---|---|
| Counts blank values? | No | Yes | No |
| Example | 3 | 2 | 1 |
Now let’s do another example of how the “Calculate” and “Filter” functions work together with the “DISTINCTCOUNT” function. In this example, we will get the number of customers who placed orders within a date range in the “Northwind” database. Because customers within an order can place their orders on different dates multiple times, we will use “DISTINCTCOUNT” instead of “COUNT”. Since the “CustomerID” field in orders in our database cannot be recorded as blank records, there is no need to use “DISTINCTCOUNTNOBLANK”. If we used “DISTINCTCOUNTNOBLANK”, the result would not change.
We will create a measure called “Customer Count in 1997” and the formula inside will be as follows.
Customer Count in 1997 =
CALCULATE(DISTINCTCOUNT(Orders[CustomerID]),FILTER(Orders,Orders[YEAR]=1997))
**Let’s do another example with COUNT to reinforce what we have learned. Let’s see how many times customers placed orders within the year 1997 using the same data. In the following example, we are counting orders with the COUNT function (COUNT(Orders[OrderID])), and we are calculating by entering 1997 as the year with the FILTER.
CountOrder of Customers in 1997 = CALCULATE(COUNT(Orders[OrderID]),FILTER(Orders,Orders[YEAR]=1997))
AHMET GÜNOĞLU
ERP Consultant and Business Intelligence Expert
https://www.ahmetgunoglu.com/blog
https://medium.com/@ahmetgunnnnn