COUNT and DISTINCTCOUNT in Power BI - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to count all rows in the Sales table.

Power BI

TotalRows = [1](Sales)

Drag options to blanks, or click blank then click option'

AAVERAGE

BDISTINCTCOUNT

CSUM

DCOUNTROWS

Attempts:

3 left

💡 Hint

Common Mistakes

Using DISTINCTCOUNT instead of COUNTROWS.

Using SUM or AVERAGE which are for numbers, not counting rows.

The COUNTROWS function counts all rows in a table. Here, it counts all rows in the Sales table.

2fill in blank

medium

Complete the code to count unique Customer IDs in the Sales table.

Power BI

UniqueCustomers = [1](Sales[CustomerID])

Drag options to blanks, or click blank then click option'

ACOUNT

BCOUNTROWS

CDISTINCTCOUNT

DSUM

Attempts:

3 left

💡 Hint

Common Mistakes

Using COUNTROWS which counts all rows, not unique values.

Using COUNT which counts non-blank values but not unique.

DISTINCTCOUNT counts unique values in a column. Here, it counts unique Customer IDs.

3fill in blank

hard

Fix the error in the code to count unique Product IDs.

Power BI

UniqueProducts = COUNTROWS([1](Sales[ProductID]))

Drag options to blanks, or click blank then click option'

ADISTINCTCOUNT

BDISTINCT

CVALUES

DALL

Attempts:

3 left

💡 Hint

Common Mistakes

Using DISTINCTCOUNT inside COUNTROWS which causes an error.

Using VALUES which can include duplicates if filters apply.

To count unique Product IDs using COUNTROWS, wrap the column with DISTINCT to get unique values as a table.

4fill in blank

hard

Fill both blanks to count all non-blank Sales amounts.

Power BI

NonBlankSales = [1](Sales[Amount]) - [2](Sales[Amount])

Drag options to blanks, or click blank then click option'

ACOUNT

BCOUNTBLANK

CDISTINCTCOUNT

DCOUNTROWS

Attempts:

3 left

💡 Hint

Common Mistakes

Using DISTINCTCOUNT which counts unique values, not blanks.

Using COUNTROWS which counts rows, not values.

COUNT counts non-blank numeric values. COUNTBLANK counts blank values. Subtracting blanks from total counts non-blank values.

5fill in blank

hard

Fill all three blanks to count unique Product IDs sold in 2023.

Power BI

UniqueProducts2023 = COUNTROWS(FILTER([1](Sales[ProductID]), Sales[Year] [2] [3]))

Drag options to blanks, or click blank then click option'

ADISTINCT

B=

C2023

DALL

Attempts:

3 left

💡 Hint

Common Mistakes

Using DISTINCTCOUNT inside FILTER which expects a table.

Using wrong comparison operators or values.

DISTINCT gets unique Product IDs as a table. FILTER keeps only rows where Year equals 2023. COUNTROWS counts those unique products.

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