Counting customers with DISTINCTCOUNT
A row in Sales is one order line. To count customers, you need the number of different values in Sales[CustomerKey], not the number of rows. DISTINCTCOUNT does exactly that, in the filter context of each cell.
Distinct counts don’t add up. A customer who bought bikes in 2024 and in 2025 is counted once in the total column, so the total can be smaller than the sum of the years. That is correct, and it is the reason a report total should never be a sum of rows.
Your task
Write a measure named Customers that returns how many different customers bought something in each cell.
About this exercise
You practise counting distinct values, not rows with DISTINCTCOUNT. The exercise runs on a small bike-shop model with a Sales fact table and Product, Customer and Date dimensions, the same star schema in every lesson.
Your measure, Customers, fills a Power BI-style matrix grouped by Product[Category] and Date[Year]. As in a real report, each cell evaluates the measure in its own filter context, and the subtotals and the grand total are evaluated again in theirs rather than added up. When you check your answer, every cell is compared with the expected value, wrong cells are marked, and the hint is about the mistake you made.
Measures that already exist in the model: Sales Amount. Reference them in square brackets, for example [Sales Amount].
Where you start
Customers =The result to build
| Year | |||
|---|---|---|---|
| Category | 2024 | 2025 | Total |
| Accessories | 9 | 9 | 9 |
| Bikes | 8 | 9 | 10 |
| Clothing | 9 | 8 | 9 |
| Total | 9 | 10 | 10 |
Last updated 10 November 2026
Opening the exercise…