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.