COUNT and DISTINCTCOUNT in Power BI - Deep Dive

Overview - COUNT and DISTINCTCOUNT

What is it?

COUNT and DISTINCTCOUNT are functions used in Power BI to count data. COUNT counts all the values in a column, excluding blanks. DISTINCTCOUNT counts only unique values, ignoring duplicates and blanks. These functions help summarize data by showing how many items or unique items exist.

Why it matters

Without COUNT and DISTINCTCOUNT, it would be hard to quickly understand how many records or unique entries exist in data. This slows down decision-making and hides important insights, like how many different customers bought a product. These functions make data summaries clear and fast.

Where it fits

Before learning COUNT and DISTINCTCOUNT, you should understand basic data tables and columns in Power BI. After mastering these, you can learn more complex aggregation functions like SUM, AVERAGE, and CALCULATE for deeper analysis.

Mental Model

Core Idea

COUNT counts every item, while DISTINCTCOUNT counts only unique items in a list.

Think of it like...

Imagine you have a basket of apples. COUNT is like counting every apple, even if some look the same. DISTINCTCOUNT is like counting how many different apple types are in the basket, ignoring repeats.

Data Column: [A, B, A, C, B]

COUNT: 5 (counts all)
DISTINCTCOUNT: 3 (counts A, B, C only once each)

Build-Up - 6 Steps

1

Foundation

Understanding COUNT function basics

Concept: COUNT counts all non-empty values in a column.

In Power BI, COUNT(column) returns the number of rows where the column has a value. It ignores blanks but counts duplicates. For example, if a column has values [1, 2, 2, blank, 3], COUNT returns 4 because it counts 1, 2, 2, and 3.

Result

COUNT returns 4 for the example data.

Knowing COUNT counts all values including duplicates helps you understand total entries, not unique ones.

2

Foundation

Understanding DISTINCTCOUNT function basics

Concept: DISTINCTCOUNT counts only unique values in a column.

DISTINCTCOUNT(column) returns how many different values appear in the column, ignoring duplicates and blanks. For example, with [1, 2, 2, blank, 3], DISTINCTCOUNT returns 3 because the unique values are 1, 2, and 3.

Result

DISTINCTCOUNT returns 3 for the example data.

DISTINCTCOUNT helps find how many unique items exist, which is useful for counting distinct customers or products.

3

Intermediate

Using COUNT with filters and conditions

🤔Before reading on: do you think COUNT counts values before or after filters are applied? Commit to your answer.

Concept: COUNT respects filters applied in reports or calculations, counting only visible rows.

If you apply a filter to show only sales from 2023, COUNT(column) counts only rows matching that filter. For example, if total rows are 100 but 30 match the filter, COUNT returns 30.

Result

COUNT returns filtered count, e.g., 30 instead of 100.

Understanding COUNT respects filters is key to dynamic, interactive reports that update counts based on user selections.

4

Intermediate

Combining DISTINCTCOUNT with slicers

🤔Before reading on: do you think DISTINCTCOUNT changes when you select different slicer options? Commit to your answer.

Concept: DISTINCTCOUNT updates dynamically with slicers or filters, showing unique counts for selected data only.

If you have a slicer for product categories, selecting one category changes DISTINCTCOUNT to count unique values only in that category. For example, if total unique customers are 50, selecting one category might show 10 unique customers.

Result

DISTINCTCOUNT changes based on slicer selection, e.g., from 50 to 10.

Knowing DISTINCTCOUNT reacts to slicers helps build interactive dashboards that reveal unique counts per user choice.

5

Advanced

Performance considerations with DISTINCTCOUNT

🤔Before reading on: do you think DISTINCTCOUNT is faster or slower than COUNT on large datasets? Commit to your answer.

Concept: DISTINCTCOUNT can be slower than COUNT on big data because it must check for uniqueness.

When working with millions of rows, DISTINCTCOUNT requires more processing to find unique values. Optimizing data model and using aggregations can improve performance. COUNT is simpler and faster since it just counts rows with values.

Result

DISTINCTCOUNT may slow report refresh or interaction on large datasets.

Understanding performance tradeoffs guides you to design efficient reports and avoid slow dashboards.

6

Expert

Using COUNT and DISTINCTCOUNT in complex DAX measures

🤔Before reading on: do you think COUNT and DISTINCTCOUNT can be combined with CALCULATE to change context? Commit to your answer.

Concept: COUNT and DISTINCTCOUNT can be wrapped in CALCULATE to modify filter context and create advanced measures.

For example, CALCULATE(DISTINCTCOUNT(Sales[CustomerID]), Sales[Region] = "West") counts unique customers only in the West region, ignoring other filters. This allows precise control over what data is counted.

Result

Measure returns unique customer count filtered by region.

Knowing how to combine these functions with CALCULATE unlocks powerful, flexible data analysis beyond simple counts.

Under the Hood

COUNT scans the column and increments a counter for each non-blank value. DISTINCTCOUNT builds a temporary set of unique values by checking each value and adding it only if not seen before, then returns the size of this set.

Why designed this way?

COUNT is designed for speed and simplicity to quickly count rows. DISTINCTCOUNT needs extra logic to identify unique values, which is more complex but essential for distinct analysis. This separation keeps simple counts fast and distinct counts accurate.

┌─────────────┐       ┌───────────────┐
│   Data      │──────▶│   COUNT       │
│ [A, B, A]   │       │ Counts all    │
└─────────────┘       │ non-blank     │
                      └───────────────┘
                          │
                          ▼
                      Result: 3

┌─────────────┐       ┌───────────────┐
│   Data      │──────▶│ DISTINCTCOUNT │
│ [A, B, A]   │       │ Builds unique │
└─────────────┘       │ set and counts│
                      └───────────────┘
                          │
                          ▼
                      Result: 2

Myth Busters - 4 Common Misconceptions

Quick: Does COUNT count blank or empty cells? Commit to yes or no.

Common Belief: COUNT counts all cells including blanks and empty ones.

Reality: COUNT ignores blank or empty cells and counts only cells with values.

Why it matters: Counting blanks inflates numbers and misleads analysis, causing wrong conclusions about data volume.

Quick: Does DISTINCTCOUNT count blanks as a unique value? Commit to yes or no.

Common Belief: DISTINCTCOUNT counts blank as a unique value.

Reality: DISTINCTCOUNT ignores blanks and does not count them as unique values.

Why it matters: Counting blanks as unique inflates distinct counts and misrepresents data uniqueness.

Quick: Is DISTINCTCOUNT always faster than COUNT? Commit to yes or no.

Common Belief: DISTINCTCOUNT is faster or equal in speed to COUNT.

Reality: DISTINCTCOUNT is slower because it must check for uniqueness, which requires more processing.

Why it matters: Assuming DISTINCTCOUNT is fast can lead to slow reports and poor user experience on large datasets.

Quick: Does COUNT count text and numbers equally? Commit to yes or no.

Common Belief: COUNT only counts numbers, ignoring text values.

Reality: COUNT counts any non-blank value, including text and numbers.

Why it matters: Misunderstanding this causes wrong expectations and errors when counting mixed data types.

Expert Zone

1

DISTINCTCOUNT performance depends heavily on data cardinality; high cardinality slows it down more.

2

COUNT can be combined with COUNTROWS and FILTER to count rows meeting complex conditions.

3

Using COUNT and DISTINCTCOUNT inside CALCULATE changes filter context, enabling advanced dynamic calculations.

When NOT to use

Avoid DISTINCTCOUNT on very large datasets with high cardinality without optimization; consider pre-aggregated tables or approximate distinct counts. Use COUNT only when duplicates matter or when counting all entries is needed.

Production Patterns

Experts use DISTINCTCOUNT to count unique customers, products, or transactions in dashboards. COUNT is used for total sales or total records. Combining with CALCULATE and FILTER creates segmented counts by region, time, or category.

Connections

Set Theory

DISTINCTCOUNT is a practical application of counting unique elements in a set.

Understanding how DISTINCTCOUNT relates to sets helps grasp why uniqueness matters and how duplicates affect data.

Database Indexing

COUNT and DISTINCTCOUNT performance depends on how data is indexed and stored in databases.

Knowing database indexing helps optimize these functions for faster queries and reports.

Inventory Management

Counting total items (COUNT) versus unique item types (DISTINCTCOUNT) is a common real-world problem in inventory control.

Relating these functions to inventory helps understand their practical use in business operations.

Common Pitfalls

#1 Counting blanks as values inflates counts.

Wrong approach:

Measure = COUNT(Table[Column]) + COUNTBLANK(Table[Column])

Correct approach:

Measure = COUNT(Table[Column])

Root cause:

Confusing COUNT with counting blanks leads to adding blank counts unnecessarily.

#2 Using COUNT when unique count is needed causes wrong insights.

Wrong approach:

Measure = COUNT(Sales[CustomerID])

Correct approach:

Measure = DISTINCTCOUNT(Sales[CustomerID])

Root cause:

Not distinguishing between total and unique counts causes incorrect business conclusions.

#3 Ignoring performance impact of DISTINCTCOUNT on large data.

Wrong approach:

Measure = DISTINCTCOUNT(BigTable[Column]) without optimization

Correct approach:

Use aggregations or pre-calculated distinct counts for large datasets

Root cause:

Lack of awareness about DISTINCTCOUNT complexity leads to slow reports.

Key Takeaways

COUNT counts all non-blank values including duplicates, giving total entries.

DISTINCTCOUNT counts only unique non-blank values, showing how many different items exist.

Both functions respect filters and slicers, making counts dynamic and interactive.

DISTINCTCOUNT is more complex and slower than COUNT, especially on large datasets.

Combining these with CALCULATE unlocks powerful, context-aware counting in 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