Dax Function: RELATEDTABLE
Category: Relationship Functions
The RELATEDTABLE function in Power BI is a DAX function that returns a table of rows from a related table. It uses existing relationships in the data model to identify and retrieve related data, working as a bridge to enable cross-table calculations.
Purpose
Fetch Related Rows: Retrieve all rows from a related table that correspond to the current context.
Enable Aggregations: Simplify the process of aggregating data from related tables.
Enhance Contextual Analysis: Combine data from multiple tables dynamically.
Type of Calculations
It performs table-level lookups by traversing relationships.
Allows dynamic aggregation and filtering based on context.
Practical Use Cases
Customer Analysis: Retrieve all orders related to a specific customer from an orders table.
Inventory Management: Identify all products belonging to a specific category.
Dynamic Filters: Apply contextual filters for more refined aggregations.
RELATEDTABLE(<table>)</table>
| Parameter | Type | Description |
|---|---|---|
Table | Table | The name of the related table whose rows you want to retrieve. |
How Does RELATEDTABLE Dax Works
The RELATEDTABLE function uses the relationships defined in the data model:
Filter Propagation: It evaluates the current row in the base table and applies context filters to the related table.
Dynamic Row Retrieval: Only rows satisfying the relationship are returned.
What Does It Return?
Returns a table of rows that are related to the current context in the base table.
When Should We Use It?
Use it when you need multiple rows of data from a related table for aggregation or further analysis.
Ideal for creating calculated columns or measures based on related rows.
Examples
Basic Usage :
Retrieve all orders for a specific customer from the Orders table:
Orders for Customer = RELATEDTABLE(Orders)
Column Usage
Count the number of orders for each customer in the Customers table:
Order Count = COUNTROWS(RELATEDTABLE(Orders))
Advanced Usage
Calculate total sales for each region by summing related sales rows:
Total Sales by Region = SUMX(RELATEDTABLE(Sales), Sales[Amount])
Tips and Tricks
Ensure Relationships Exist: The function relies on active relationships; verify your data model connections.
Use Aggregation Functions: Combine with functions like
COUNTROWSorSUMXfor summarized outputs.Avoid Overuse in Large Models: Use sparingly in high-cardinality scenarios to minimize performance impact.
Performance Impact of RELATEDTABLE DAX Function:
Can impact performance on large datasets with high-cardinality relationships.
Use indexes and properly designed relationships to optimize.
Related Functions You Might Need
RELATED: Fetches a single value from a related table.
LOOKUPVALUE: Retrieves a specific value based on a condition without requiring relationships.
CALCULATETABLE: Creates a table with context-modified filters.
Want to Learn More?
For more information, check out the official Microsoft documentation for RELATEDTABLE 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 all rows from a related table that match the current row or context.
No, it requires an active relationship between tables.
RELATEDTABLE returns multiple rows as a table, while RELATED returns a single value.
Yes, it is often combined with aggregation functions like SUMX or COUNTROWS.
It can be slower for high-cardinality relationships; optimize your model for better performance.