Data modeling is a foundational skill in Power BI, and mastering DAX functions that operate across related tables is essential for creating powerful and efficient reports. Two of the most useful functions for working with relationships in Power BI are RELATED and RELATEDTABLE.
In this blog, we will explore what these functions do, when to use each, and how they work with sample data. We will also discuss the importance of relationships in enabling these functions to deliver accurate results. By the end, you’ll be equipped to use RELATED and RELATEDTABLE effectively in your Power BI projects.
Why Relationships Matter in Power BI
Relationships are the backbone of any Power BI data model. They define how tables connect and interact with each other. Typically, tables are related through keys — for example, CustomerID in a Customers table and CustomerID in an Orders table.
Key aspects of relationships include:
- Cardinality: Whether the relationship is one-to-one, one-to-many, or many-to-many.
- Direction: The flow of filters between tables (usually from one to many).
- Active Status: Only one relationship between two tables can be active at a time.
Both RELATED and RELATEDTABLE depend on these relationships to fetch or aggregate data across tables correctly.
Sample Data:
Let’s try to analyse the difference between Related & RelatedTable with sample data by using 2 tables.
Customers Table (Dimension Table)
Contains descriptive attributes such as:
Orders Table (Fact Table)
Contains transactional attributes and numeric measures:
A one-to-many relationship is created in Power BI from: Customers[CustomerID] → Orders[CustomerID], with Customers on the one side and Orders on the many side.
RELATED Function
The RELATED function returns a single related value from another table and is used on the many side of a relationship to fetch data from the one side.
Example:
To add the Customer Name from the Customers table(One side) to the Orders table(Many side).
Dax: CustomerName_Related = RELATED(‘Customers Table'[CustomerName])
Result: The Orders table now includes a CustomerName column with the correct customer name for every order.
RELATEDTABLE Function
The RELATEDTABLE function returns a table of rows from a related table (usually the many side) that are linked to the current row in the one-side table. This makes it ideal for row-level aggregations in calculated columns.
Example:
To count how many orders each customer has placed, create a calculated column in the Customers table:
DAX: OrderCount = COUNTROWS(RELATEDTABLE(Orders))
Result: The Customers table now shows the number of orders placed by each customer.
When to Use RELATED vs RELATEDTABLE
Use Case | RELATED | RELATEDTABLE |
Fetch a single related value in a calculated column (e.g., customer name on orders) | Yes | No |
Count or aggregate related rows in a calculated column (e.g., number of orders per customer) | No | Yes |
Perform dynamic aggregations in measures | No (use CALCULATE) | No (use CALCULATE) |
Best Practices and Important Considerations
- Make sure relationships between tables are correctly defined and active in your data model.
- Use RELATED mostly in calculated columns on the many side tables to pull single values from the one side.
- Use RELATEDTABLE in calculated columns on the one side table to summarize or count related rows from the many side.
- For aggregations and calculations in reports, prefer using measures with CALCULATE instead of calculated columns.
- Be aware that calculated columns increase the size of your data model and are computed during data refresh.
Summary
- RELATED retrieves a single related value from the one side table to the many side.
- RELATEDTABLE returns all related rows from the many side to the one side, enabling aggregation in calculated columns.
- Both functions rely on proper, active relationships to work correctly.
- Understanding when and how to use these functions improves data modelling efficiency and report quality.