COUNT and DISTINCTCOUNT in Power BI - Step-by-Step Guide

Introduction

These two functions help you count items in your data. COUNT counts all numbers or non-blank values in a column. DISTINCTCOUNT counts only unique values, ignoring duplicates. They help you understand how many items or unique items you have.

When you want to know how many sales transactions happened in total.

When you need to find out how many different customers bought products.

When you want to count how many products were sold, including duplicates.

When you want to count how many unique product categories appear in your sales data.

When you want to check how many dates have sales records.

Steps

Step 1: Open your Power BI Desktop file

- Power BI Desktop main window

Your report and data model are visible

Step 2: Click on the Modeling tab

- Ribbon at the top

Modeling options appear

Step 3: Click New Measure

- Modeling tab

A formula bar appears to enter a DAX formula

Step 4: Type the formula for COUNT, for example: Total Sales Count = COUNT(Sales[Quantity])

- Formula bar

A new measure named Total Sales Count is created that counts all Quantity values

Step 5: Click New Measure again

- Modeling tab

Formula bar appears for a new measure

Step 6: Type the formula for DISTINCTCOUNT, for example: Unique Customers = DISTINCTCOUNT(Sales[CustomerID])

- Formula bar

A new measure named Unique Customers is created that counts unique CustomerID values

Step 7: Add a card visual to the report canvas

- Visualizations pane

An empty card appears on the canvas

Step 8: Drag the Total Sales Count measure to the card

- Fields pane to card visual

The card shows the total count of sales quantities

Step 9: Add another card visual and drag the Unique Customers measure to it

- Visualizations pane and Fields pane

The second card shows the count of unique customers

Before vs After

Before

Report canvas has no cards and no measures for counting

After

Report canvas shows two cards: one with total count of sales quantities (e.g., 1500), another with unique customer count (e.g., 120)

Settings Reference

New Measure

📍 Modeling tab in the ribbon

To create custom calculations like COUNT and DISTINCTCOUNT

Default: No measure created

Visualizations pane

📍 Right side of Power BI Desktop

To add visuals that display your data and measures

Default: No visual selected

Common Mistakes

Using COUNT on a text column

COUNT only counts numbers and non-blank values, so it may return unexpected results or zero

Use DISTINCTCOUNT for text columns to count unique text values or use COUNTROWS with filters

Confusing COUNT with COUNTROWS

COUNT counts values in a single column, COUNTROWS counts rows in a table or filtered table

Use COUNT for counting values in one column, COUNTROWS to count rows in a table or filtered table

Summary

COUNT counts all values in a column, including duplicates.

DISTINCTCOUNT counts only unique values in a column.

Use these measures to quickly see totals and unique counts in your reports.

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