Dax Function: RELATED
Category: Relationship Functions
The RELATED function in Power BI is a DAX function that retrieves related data from another table based on an established relationship. It is commonly used in calculated columns and measures to access values from a related table.
Purpose
Access Related Data: Fetch values from a related table using existing relationships in the model.
Enable Contextual Analysis: Combine data from related tables for meaningful insights.
Streamline Calculations: Simplify expressions by directly referencing related table columns.
Type of Calculations
It performs lookups across tables with one-to-one or many-to-one relationships.
Combines and integrates data from multiple tables for custom calculations.
Practical Use Cases
Data Enrichment: Add additional context by pulling fields from lookup tables.
Price Calculations: Retrieve price information from a product table to compute revenue in a sales table.
Region Mapping: Map geographic information from a region table to transaction data.
RELATED(<column>)</column>
| Parameter | Type | Description |
|---|---|---|
Column | Column | A column from a related table. This column must have a defined relationship. |
How Does RELATED Dax Works
The RELATED function leverages the existing relationship between tables in the model to retrieve a value:
Relationship Direction: The function requires a one-to-many or one-to-one relationship.
Filter Context: The function applies the current filter context to identify the relevant value.
What Does It Return?
The function returns a single value from the specified column in the related table, based on the active relationship.
When Should We Use It?
When you need to bring in data from a lookup or related table into the current table.
To calculate derived fields based on data from multiple tables.
For creating calculated columns or measures that depend on related data.
Examples
Basic Usage :
Retrieve the product category from the Products table into the Sales table:
Product Category =
RELATED(Products[Category])
Column Usage
Calculate revenue by multiplying Quantity from the Sales table with Price from the Products table:
Revenue =
Sales[Quantity] * RELATED(Products[Price])
Advanced Usage
Combine RELATED with other functions to calculate total revenue by category:
Total Revenue by Category =
CALCULATE(
SUMX(Sales, Sales[Quantity] * RELATED(Products[Price])),
ALLEXCEPT(Products, Products[Category])
)
Tips and Tricks
Check Relationships: Ensure that relationships are correctly defined in the model for the function to work.
Optimize Model Design: Use the function judiciously to avoid performance bottlenecks with large datasets.
Test with Filter Contexts: Validate the output in different contexts to ensure the function behaves as expected.
Performance Impact of RELATED DAX Function:
Efficient for small to medium-sized datasets but can impact performance in large models.
Avoid using RELATED in every row for high-cardinality tables.
Related Functions You Might Need
RELATEDTABLE: Retrieves an entire table of related rows instead of a single value.
LOOKUPVALUE: Performs lookups without requiring a direct relationship.
CALCULATE: Changes the filter context for calculations.
ALLEXCEPT: Removes filters except for the specified columns.
Want to Learn More?
For more information, check out the official Microsoft documentation for RELATED 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.
It retrieves a value from a related table based on an existing relationship.
No, the RELATED function requires a defined relationship in the model.
RELATED returns a single value, while RELATEDTABLE returns a table of related rows.
Yes, it can be used in measures but is more commonly applied in calculated columns.
The RELATED function only uses active relationships. For inactive relationships, use the USERELATIONSHIP function with CALCULATE.