VALUES and DISTINCT in Power BI - Deep Dive

Overview - VALUES and DISTINCT

What is it?

VALUES and DISTINCT are two functions in Power BI's DAX language used to get unique values from a column or table. VALUES returns a one-column table of unique values, including blanks if present. DISTINCT also returns unique values but excludes blanks and handles blanks slightly differently. Both help summarize or filter data by removing repeated entries.

Why it matters

Without these functions, you would struggle to analyze data without repeated values cluttering your results. They help you create clean lists, filters, and summaries that reflect the true variety in your data. This makes reports clearer and decisions more accurate because you focus on unique items, not repeated noise.

Where it fits

Before learning VALUES and DISTINCT, you should understand basic DAX functions and tables. After mastering them, you can explore advanced filtering, relationships, and context manipulation in Power BI to build dynamic reports.

Mental Model

Core Idea

VALUES and DISTINCT both extract unique items from data, but VALUES can include blanks and respects filter context, while DISTINCT returns unique non-blank values.

Think of it like...

Imagine you have a basket of mixed fruits with some repeated apples and oranges. VALUES is like picking out one of each fruit including any empty spots in the basket, while DISTINCT is like picking one of each fruit but ignoring empty spots.

┌───────────────┐       ┌───────────────┐
│   Original    │       │   Unique Set  │
│  Data Table   │──────▶│ VALUES or     │
│ (with repeats)│       │ DISTINCT      │
└───────────────┘       └───────────────┘
          │                      │
          │                      ▼
          │             ┌─────────────────┐
          │             │ List of unique  │
          │             │ values (may     │
          │             │ include blanks) │
          │             └─────────────────┘

Build-Up - 6 Steps

1

Foundation

Understanding Unique Values in Data

Concept: Learn what unique values mean and why they matter in data analysis.

When you look at a list of items, some may appear many times. Unique values are the items that appear only once or are counted once regardless of repeats. For example, in a list of colors: red, blue, red, green, blue, the unique colors are red, blue, and green.

Result

You can identify the distinct items in any list or column.

Understanding unique values is the base for summarizing and filtering data effectively.

2

Foundation

Introduction to VALUES Function

Concept: VALUES returns a table of unique values from a column, including blanks if any.

VALUES(Column) returns a one-column table with all unique values from that column. If the column has blanks, VALUES includes a blank row. It also respects the current filter context, meaning it shows unique values only from filtered data.

Result

You get a clean list of unique values including blanks, useful for slicers or filters.

Knowing VALUES respects filters helps you build dynamic reports that react to user selections.

3

Intermediate

Introduction to DISTINCT Function

Concept: DISTINCT returns unique values from a column but excludes blanks and handles blanks differently.

DISTINCT(Column) returns a one-column table of unique values, similar to VALUES but it does not return blank rows if the column has blanks. It also respects filter context but treats blanks differently.

Result

You get a list of unique non-blank values, useful when blanks should be ignored.

Understanding DISTINCT's blank handling helps avoid unexpected blanks in your results.

4

Intermediate

Comparing VALUES and DISTINCT Behavior

🤔Before reading on: do you think VALUES and DISTINCT always return the same results? Commit to yes or no.

Concept: Explore the subtle differences between VALUES and DISTINCT, especially with blanks and filter context.

VALUES includes blank rows if blanks exist in the column, DISTINCT excludes them. VALUES returns unique values respecting filters, DISTINCT does too but blanks are treated differently. For example, if a column has values {A, B, blank, A}, VALUES returns {A, B, blank}, DISTINCT returns {A, B}.

Result

You can choose the right function based on whether you want blanks included or not.

Knowing these differences prevents bugs where blanks unexpectedly appear or disappear in reports.

5

Advanced

Using VALUES and DISTINCT in Measures

🤔Before reading on: do you think VALUES can be used inside a measure to dynamically filter data? Commit to yes or no.

Concept: Learn how to use VALUES and DISTINCT inside DAX measures to create dynamic calculations.

You can use VALUES inside CALCULATE or FILTER to get unique values based on current filters. For example, COUNTROWS(VALUES(Table[Column])) counts unique values dynamically. DISTINCT can be used similarly but may exclude blanks, affecting results.

Result

Measures become responsive to user selections and show accurate unique counts or filters.

Understanding how these functions interact with filter context unlocks powerful dynamic reporting.

6

Expert

Unexpected Blanks and Filter Context Effects

🤔Before reading on: do you think VALUES always returns blanks only if they exist in the raw data? Commit to yes or no.

Concept: Discover how filter context and relationships can cause VALUES to return blanks unexpectedly.

VALUES returns blanks if the current filter context includes rows with blanks or if relationships cause blank rows to appear. For example, in a relationship between tables, filtering one table can cause VALUES on another to return blanks due to missing matches. DISTINCT does not return these blanks, which can cause subtle differences in results.

Result

You learn to diagnose and fix confusing blanks in reports caused by filter context and relationships.

Knowing how filter context and relationships affect VALUES prevents hard-to-find bugs in complex models.

Under the Hood

VALUES and DISTINCT work by scanning the specified column in the current filter context to build a temporary one-column table of unique values. VALUES includes blank rows if any exist in the filtered data, while DISTINCT excludes blanks. Internally, VALUES also respects relationships and filters, which can cause blanks to appear if no matching rows exist. DISTINCT performs a distinct operation ignoring blanks, making it slightly simpler.

Why designed this way?

VALUES was designed to support dynamic filtering and relationship-aware calculations, including blanks to represent missing or unknown data. DISTINCT was introduced as a simpler function for unique value extraction without blanks, useful for cleaner lists. This separation allows users to choose behavior based on their reporting needs.

┌───────────────┐
│ Input Column  │
│ (with repeats │
│ and blanks)   │
└──────┬────────┘
       │
       ▼
┌───────────────┐       ┌───────────────┐
│ VALUES        │       │ DISTINCT      │
│ - Includes    │       │ - Excludes    │
│   blanks      │       │   blanks      │
│ - Respects    │       │ - Respects    │
│   filter      │       │   filter      │
│   context     │       │   context     │
└──────┬────────┘       └──────┬────────┘
       │                       │
       ▼                       ▼
┌───────────────┐       ┌───────────────┐
│ Unique values │       │ Unique values │
│ (may include  │       │ (no blanks)   │
│ blanks)       │       │               │
└───────────────┘       └───────────────┘

Myth Busters - 3 Common Misconceptions

Quick: Do VALUES and DISTINCT always return the exact same list of unique values? Commit to yes or no.

Common Belief: VALUES and DISTINCT are interchangeable and always return the same unique values.

Reality: VALUES includes blank rows if blanks exist in the data or filter context, while DISTINCT excludes blanks. This can cause different results.

Why it matters: Using the wrong function can cause unexpected blanks or missing values in reports, confusing users and leading to wrong conclusions.

Quick: Does VALUES only return blanks if blanks exist in the raw data? Commit to yes or no.

Common Belief: VALUES returns blanks only if the original data column has blank values.

Reality: VALUES can return blanks due to filter context or relationships even if the raw data column has no blanks, because missing matches appear as blanks.

Why it matters: This can cause unexpected blanks in reports, making troubleshooting difficult if you assume blanks come only from raw data.

Quick: Can DISTINCT be used to count unique values including blanks? Commit to yes or no.

Common Belief: DISTINCT counts unique values including blanks just like VALUES.

Reality: DISTINCT excludes blanks, so counting unique values with DISTINCT ignores blanks, which can undercount if blanks matter.

Why it matters: Miscounting unique values can mislead business decisions, especially when blanks represent meaningful missing data.

Expert Zone

1

VALUES returns a blank row when the filter context includes blanks or missing relationships, which can be used intentionally to detect missing data.

2

DISTINCT is slightly faster than VALUES because it does not handle blanks or filter context as intricately, useful for large datasets where blanks are irrelevant.

3

Using VALUES inside CALCULATE can change filter context dynamically, enabling advanced dynamic filtering patterns not possible with DISTINCT.

When NOT to use

Avoid VALUES when you want to exclude blanks or when performance is critical and blanks are irrelevant; use DISTINCT instead. Conversely, avoid DISTINCT when blanks represent important missing data or when you need to respect complex filter contexts and relationships.

Production Patterns

In production, VALUES is often used in slicers and dynamic filters to show all possible options including blanks. DISTINCT is used in calculated columns or measures where blanks should be ignored for cleaner summaries. Combining these with FILTER and CALCULATE enables powerful dynamic reports that respond to user selections and data relationships.

Connections

Filter Context in DAX

VALUES and DISTINCT both depend on filter context to determine which rows to consider unique.

Understanding filter context helps explain why VALUES and DISTINCT return different results depending on report filters and slicers.

Relational Database Joins

VALUES returning blanks due to missing relationships is similar to how LEFT JOINs in SQL produce NULLs for unmatched rows.

Knowing SQL joins helps understand why blanks appear in VALUES when relationships filter data without matches.

Set Theory in Mathematics

VALUES and DISTINCT perform set operations to extract unique elements from a collection, similar to how sets contain unique elements.

Recognizing these functions as set operations clarifies their purpose and behavior in data analysis.

Common Pitfalls

#1 Unexpected blanks appear in unique value lists causing confusion.

Wrong approach:

UniqueValues = DISTINCT(Table[Column])
// User expects blanks included but they are missing

Correct approach:

UniqueValues = VALUES(Table[Column])
// Includes blanks if present

Root cause:

Misunderstanding that DISTINCT excludes blanks while VALUES includes them.

#2 Counting unique values undercounts because blanks are ignored.

Wrong approach:

UniqueCount = COUNTROWS(DISTINCT(Table[Column]))
// Blanks ignored, count lower than expected

Correct approach:

UniqueCount = COUNTROWS(VALUES(Table[Column]))
// Blanks included in count

Root cause:

Not realizing DISTINCT excludes blanks in its unique value list.

#3 VALUES returns blanks unexpectedly due to filter context and relationships.

Wrong approach:

UniqueValues = VALUES(RelatedTable[Column])
// Blanks appear unexpectedly

Correct approach:

Use FILTER or ISBLANK checks to handle blanks explicitly:
UniqueValues = FILTER(VALUES(RelatedTable[Column]), NOT(ISBLANK(RelatedTable[Column])))

Root cause:

Not accounting for how filter context and relationships cause blanks to appear in VALUES.

Key Takeaways

VALUES and DISTINCT both return unique values but differ mainly in how they handle blanks and filter context.

VALUES includes blanks and respects relationships, making it suitable for dynamic filtering and detecting missing data.

DISTINCT excludes blanks and is simpler and sometimes faster, useful when blanks are irrelevant.

Understanding filter context and relationships is key to predicting how these functions behave in reports.

Choosing the right function prevents confusing blanks or missing values, ensuring accurate and clear business intelligence reports.