COUNT and DISTINCTCOUNT in Power BI - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ dax_lod_result

intermediate

Calculate total sales count with COUNT

You have a Sales table with a column OrderID. Which DAX measure correctly counts all sales orders, including duplicates?

A Total Sales Count = COUNT(Sales[OrderID])

B Total Sales Count = DISTINCTCOUNT(Sales[OrderID])

C Total Sales Count = COUNTROWS(Sales)

D Total Sales Count = COUNT(Sales)

💡 Hint

Think about counting all rows with a specific column, including duplicates.

COUNT counts all non-blank values in a column, including duplicates. DISTINCTCOUNT counts unique values only. COUNTROWS counts rows but does not count a specific column's values. COUNT(Sales) is invalid because COUNT requires a column.

❓ visualization

intermediate

Choose the best visual for comparing COUNT and DISTINCTCOUNT

You want to show the difference between total sales orders and unique customers in a report. Which visual is best to clearly show both counts side by side?

A A clustered column chart with two bars: one for total orders (COUNT) and one for unique customers (DISTINCTCOUNT)

B A pie chart showing total orders only

C A line chart showing total orders over time only

D A table listing all orders without aggregation

💡 Hint

Think about comparing two related numbers side by side.

A clustered column chart lets you compare two measures side by side clearly. Pie charts and line charts show one measure or trends, not direct comparison. A table without aggregation doesn't summarize counts.

🧠 Conceptual

advanced

Understanding COUNT vs DISTINCTCOUNT behavior with blanks

Given a column with values: {1, 2, 2, BLANK, 3, BLANK}, what are the results of COUNT and DISTINCTCOUNT on this column?

A COUNT = 6, DISTINCTCOUNT = 4

B COUNT = 4, DISTINCTCOUNT = 3

C COUNT = 5, DISTINCTCOUNT = 3

D COUNT = 4, DISTINCTCOUNT = 4

💡 Hint

Remember that COUNT ignores blanks but DISTINCTCOUNT counts unique non-blank values.

COUNT counts non-blank values: 1, 2, 2, 3 → 4 values. DISTINCTCOUNT counts unique non-blank values: 1, 2, 3 → 3 values.

🔧 Formula Fix

advanced

Identify the error in this DAX measure using COUNT

What error will this DAX measure cause?
TotalCount = COUNT(Sales)

Power BI

TotalCount = COUNT(Sales)

A Returns distinct count of Sales table

B Returns total rows count of Sales table

C Syntax error: COUNT requires a column, not a table

D Runtime error: invalid function usage

💡 Hint

Check the function argument type requirements.

COUNT requires a single column as argument. Passing a table causes a syntax error.

🎯 Scenario

expert

Calculate unique customers per region excluding blanks

You have a Customer table with columns CustomerID and Region. Some CustomerID values are blank. You want a measure that counts unique customers per region, ignoring blanks. Which DAX measure is correct?

A Unique Customers = DISTINCTCOUNT(Customer[CustomerID])

B Unique Customers = COUNTROWS(FILTER(Customer, NOT(ISBLANK(Customer[CustomerID]))))

C Unique Customers = COUNT(Customer[CustomerID])

D Unique Customers = CALCULATE(DISTINCTCOUNT(Customer[CustomerID]), Customer[CustomerID] <> BLANK())

💡 Hint

Think about filtering out blanks before counting distinct customers.

Option D uses CALCULATE with a filter to exclude blanks before DISTINCTCOUNT. Option D counts distinct including blanks (which DISTINCTCOUNT ignores anyway, but filter is explicit). Option D counts all non-blank values including duplicates. Option D counts rows but not distinct customers.

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