Power BI DAX: CALCULATE, DISTINCTCOUNT
Mohamad's interest is in Programming (Mobile, Web, Database and Machine Learning). He is studying at the Center For Artificial Intelligence Technology (CAIT), Universiti Kebangsaan Malaysia (UKM).
Product Categories Sold =
CALCULATE(
DISTINCTCOUNT('Product'[ProductCategory]),
'Date'
)
DISTINCTCOUNT is a DAX function that counts the number of unique values in a column. It's particularly useful when you need to count distinct items, such as unique product categories, customers, or any other distinct entities.
Example Scenario
Imagine you have a sales dataset with various products sold over different dates. Each product belongs to a specific category. You want to create a measure that counts the number of unique product categories that had sales during a selected period.
DAX Expression
To achieve this, you can use the DISTINCTCOUNT function within a CALCULATE function to filter the data based on the selected period.
Product Categories Sold =
CALCULATE(
DISTINCTCOUNT('Product'[ProductCategory]),
'Date'
)
Illustration
Consider the following context:
| Date | Product | ProductCategory |
| 2024-09-01 | Product A | Category 1 |
| 2024-09-02 | Product B | Category 2 |
| 2024-09-03 | Product C | Category 1 |
| 2024-09-04 | Product D | Category 3 |
| 2024-09-05 | Product E | Category 2 |
If you select the period from 2024-09-01 to 2024-09-05, the measure Product Categories Sold will count the number of unique product categories that had sales during this period. In this case, the result would be 3 (Category 1, Category 2, and Category 3).
This measure dynamically calculates the number of distinct product categories based on the selected date range, providing valuable insights into the diversity of products sold over time.