COUNT and DISTINCTCOUNT in Power BI - Real Business Scenario

Scenario Mode

👤 Your Role: You are a sales analyst at a retail company.

📋 Request: Your manager wants to understand how many total sales transactions happened each month and how many unique customers made purchases each month.

📊 Data: You have a sales data table with columns: TransactionID, CustomerID, SaleDate, and Amount.

🎯 Deliverable: Create a report showing for each month the total number of sales transactions and the number of unique customers.

Sample Data

TransactionID	CustomerID	SaleDate	Amount
1001	C001	2024-01-05	150
1002	C002	2024-01-15	200
1003	C001	2024-01-20	100
1004	C003	2024-02-02	250
1005	C004	2024-02-10	300
1006	C002	2024-02-15	180
1007	C005	2024-03-01	220
1008	C001	2024-03-05	130
1009	C006	2024-03-10	170
1010	C005	2024-03-15	210

1

Step 1: Create a new column in Power BI to extract the month and year from the SaleDate column.

MonthYear = FORMAT(Sales[SaleDate], "YYYY-MM")

Expected Result

A new column 'MonthYear' with values like '2024-01', '2024-02', '2024-03' for each row.

2

Step 2: Create a measure to count total sales transactions per month.

Total Transactions = COUNT(Sales[TransactionID])

Expected Result

For January 2024: 3, February 2024: 3, March 2024: 4

3

Step 3: Create a measure to count distinct customers per month.

Unique Customers = DISTINCTCOUNT(Sales[CustomerID])

Expected Result

For January 2024: 2, February 2024: 3, March 2024: 3

4

Step 4: Create a table visual in Power BI with MonthYear as rows, and add the two measures: Total Transactions and Unique Customers as values.

Table visual configuration: Rows = MonthYear, Values = Total Transactions, Unique Customers

Expected Result

A table showing each month with total transactions and unique customers counts.

Final Result

MonthYear | Total Transactions | Unique Customers
---------------------------------------------
2024-01   | 3                  | 2
2024-02   | 3                  | 3
2024-03   | 4                  | 3

✓January had 3 sales transactions from 2 unique customers.

✓February had 3 sales transactions from 3 unique customers.

✓March had the highest activity with 4 sales transactions from 3 unique customers.

Bonus Challenge

Create a line chart showing the trend of unique customers over the months.

Show Hint

Use MonthYear on the X-axis and the Unique Customers measure on the Y-axis.

Practice

1.

What does the DISTINCTCOUNT function do in Power BI?

easy

A. Counts all values including empty ones in a column

B. Counts the total number of rows in a table

C. Counts only empty values in a column

D. Counts the number of unique non-empty values in a column

Solution

1. Step 1: Understand DISTINCTCOUNT purpose

   DISTINCTCOUNT counts unique non-empty values in a column, ignoring duplicates and blanks.

2. Step 2: Compare with other options

   Other options describe counting all values, empty values, or rows, which are incorrect for DISTINCTCOUNT.

3. Final Answer:

   Counts the number of unique non-empty values in a column -> Option D

4. Quick Check:

   DISTINCTCOUNT = unique non-empty count [OK]

Hint: DISTINCTCOUNT counts unique values only, ignoring blanks [OK]

Common Mistakes:

• Confusing DISTINCTCOUNT with COUNT
• Thinking it counts empty or blank values
• Assuming it counts total rows

2.

Which of the following is the correct syntax to count all non-empty values in the Sales[OrderID] column?

?

easy

A. DISTINCTCOUNT(Sales[OrderID])

B. COUNT(Sales[OrderID])

C. COUNTROWS(Sales[OrderID])

D. COUNTBLANK(Sales[OrderID])

Solution

1. Step 1: Identify COUNT syntax

   COUNT(column) counts all non-empty values in the specified column.

2. Step 2: Eliminate incorrect options

   DISTINCTCOUNT counts unique values, COUNTROWS requires a table, COUNTBLANK counts blanks, so only COUNT is correct here.

3. Final Answer:

   COUNT(Sales[OrderID]) -> Option B

4. Quick Check:

   COUNT(column) counts non-empty values [OK]

Hint: Use COUNT(column) to count non-empty values [OK]

Common Mistakes:

• Using COUNTROWS on a column instead of a table
• Confusing DISTINCTCOUNT with COUNT
• Using COUNTBLANK instead of COUNT

3.

Given the table Products with the Category column containing values: {"Fruit", "Fruit", "Vegetable", "Fruit", "Vegetable", "Grain"}, what is the result of DISTINCTCOUNT(Products[Category])?

medium

A. 3

B. 6

C. 5

D. 1

Solution

1. Step 1: Identify unique categories

   The unique values in Category are "Fruit", "Vegetable", and "Grain" which totals 3.

2. Step 2: Apply DISTINCTCOUNT

   DISTINCTCOUNT counts these unique non-empty values, so the result is 3.

3. Final Answer:

   3 -> Option A

4. Quick Check:

   DISTINCTCOUNT = 3 unique categories [OK]

Hint: Count unique values, ignore duplicates [OK]

Common Mistakes:

• Counting total rows instead of unique values
• Confusing DISTINCTCOUNT with COUNT
• Including duplicates in count

4.

Consider this DAX measure:
UniqueCustomers = COUNT(Customer[CustomerID])
Why might this measure give an incorrect count of unique customers?

medium

A. Because CustomerID column is not numeric

B. Because COUNT only counts blank values

C. Because COUNT counts all non-empty values, including duplicates

D. Because COUNT counts unique values only

Solution

1. Step 1: Understand COUNT behavior

   COUNT counts all non-empty values, including duplicates, so it does not count unique customers.

2. Step 2: Identify correct function for unique count

   To count unique customers, DISTINCTCOUNT should be used instead of COUNT.

3. Final Answer:

   Because COUNT counts all non-empty values, including duplicates -> Option C

4. Quick Check:

   COUNT counts duplicates, use DISTINCTCOUNT for unique [OK]

Hint: Use DISTINCTCOUNT for unique counts, not COUNT [OK]

Common Mistakes:

• Assuming COUNT counts unique values
• Thinking COUNT counts blanks
• Believing CustomerID must be numeric for COUNT

5.

You have a sales table with 1000 rows. The CustomerID column has 900 non-empty values, but only 850 unique customers. Which DAX measure correctly returns the number of unique customers who made a purchase?

hard

A. DISTINCTCOUNT(Sales[CustomerID])

B. COUNTROWS(Sales)

C. COUNT(Sales[CustomerID])

D. COUNTBLANK(Sales[CustomerID])

Solution

1. Step 1: Understand the data counts

   There are 900 non-empty CustomerID values but only 850 unique customers.

2. Step 2: Choose measure for unique count

   DISTINCTCOUNT returns the count of unique non-empty values, which is 850, the correct unique customer count.

3. Final Answer:

   DISTINCTCOUNT(Sales[CustomerID]) -> Option A

4. Quick Check:

   Use DISTINCTCOUNT for unique customer count [OK]

Hint: Use DISTINCTCOUNT to get unique counts, not COUNT [OK]

Common Mistakes:

• Using COUNT which counts duplicates
• Using COUNTROWS which counts all rows
• Using COUNTBLANK which counts blanks