Dax Function: VALUES

Category: Table Manipulation Functions

The VALUES function in Power BI is a DAX (Data Analysis Expressions) function that returns a single-column table containing the unique values in a column or all rows from a table if the input is a table. It is widely used for filtering, relationships, and dynamic calculations.

Purpose

  • Filter Context: Extract distinct values from a column to create filters.

  • Dynamic Relationships: Build flexible calculations based on selected or existing values.

  • Summarization: Serve as a basis for aggregating unique values or data points.

Type of Calculations

  • Extraction: Identifies unique values from a column.

  • Row Preservation: Returns all rows if applied to a table.

  • Contextual Analysis: Adapts to the current filter context dynamically.

Practical Use Cases

  1. Dynamic Reporting: Display unique categories or labels dynamically in visuals.

  2. Custom Calculations: Filter calculations to operate only on distinct values.

  3. Creating Relationships: Generate unique keys for connecting unrelated tables.

  4. Scenario Analysis: Analyze subsets of data based on specific selections.


VALUES(<tableorcolumn>)</tableorcolumn>
ParameterTypeDescription
TableOrColumnColumn/TableA column to return unique values from, or a table to return all rows.

How Does VALUES Dax Works

  1. Input Column: When you pass a column, the function identifies and returns its unique values.

  2. Input Table: If a table is passed, it returns all rows without deduplication.

  3. Filter Context Sensitivity: The result adapts to the active filter context, returning only the visible values in the context.

Key Behavior:

  • If no filter context exists, the function behaves similarly to the DISTINCT function.

  • If used with BLANK values, those blanks are included unless explicitly removed.

Logical Principle

  • The VALUES function operates as a summarizer by extracting unique entries within the active data context.

  • It is used in filters, measures, and calculated columns to simplify or focus the dataset.

What Does It Return?

  • Type: Single-column table or entire table.

  • Content:

    • A table of distinct values if a column is passed.

    • The entire table if a table is passed.

When Should We Use It?

  • Building Relationships: Generate unique values for a table to act as a relationship key.

  • Dynamic Aggregations: Ensure calculations occur on a distinct set of values only.

  • Hierarchical Filters: Extract a single level of hierarchy dynamically.

Examples

Basic Usage :

Retrieve Unique Values:


VALUES(Sales[ProductCategory])

Result: Returns a table with unique product categories from the Sales table.

Column Usage

Unique Filter in a Measure:


SalesByCategory =
SUMX(VALUES(Sales[ProductCategory]), CALCULATE(SUM(Sales[Revenue])))

Effect: Aggregates revenue by unique product categories.

Advanced Usage

Dynamic Relationships:


DistinctCustomers = VALUES(Sales[CustomerID])

Use Case: Creates a calculated table of unique customer IDs for building relationships.

Tips and Tricks

  • Use VALUES instead of DISTINCT when interacting with filter contexts.

  • Combine with CALCULATE or FILTER for complex scenarios.

  • Passing an entire table can lead to unintended results; specify a column whenever possible.

  • Ensure consistent column names if combining results from multiple tables.

Performance Impact of VALUES DAX Function:

  • Use with caution in large datasets; filtering dynamically can be resource-intensive.

  • Optimize by applying additional filters or context constraints.

Related Functions You Might Need

FunctionDescription
DISTINCTReturns distinct values but is not context-sensitive.
ALLIgnores any filters and returns all rows.
CALCULATETABLEModifies a table expression with filters.
FILTERFilters rows based on a condition.

Want to Learn More?
For more information, check out the official Microsoft documentation for VALUES You can also experiment with this function in your Power BI reports to explore its capabilities.

Unlock the full capabilities of Power BI and elevate your data insights with our specialized consulting services. Whether you need guidance on advanced DAX functions like those highlighted here, support in designing interactive dashboards, or expertise in optimizing data models for enhanced performance, our experienced Power BI consultants are equipped to deliver customized solutions for your business. Explore our Power BI Consulting Services page to discover how we can help your organization make smarter, data-driven decisions.

1. What does the VALUES function do in Power BI?

The VALUES function returns a table of unique values from a column or all rows from a table.

2. How does VALUES differ from DISTINCT?

VALUES is filter-context sensitive and returns results based on the active filter, while DISTINCT ignores context.

3. Can VALUES handle blank values?

Yes, VALUES includes blank values unless explicitly filtered out.

4. Is VALUES suitable for creating relationships?

Yes, it is commonly used to create unique lists for relationships.

5. Can VALUES be used with measures?

Yes, it is often combined with CALCULATE or SUMX to perform calculations on unique values.

Previous article
Dax Function: UNION
Back to module