Lookup Values with RELATED and RELATEDTABLE in Power BI

When working with related tables in Power BI, understanding how to use the DAX functions RELATED and RELATEDTABLE is essential. These functions help you leverage relationships between tables, enabling you to retrieve or aggregate data efficiently. In this blog, we’ll explore how these functions work and demonstrate how to create an aggregated column with RELATEDTABLE.

1. Using RELATED in Power BI

What is RELATED?

The RELATED function is used to retrieve a value from a related table based on the relationship defined in the data model.

Syntax:

RELATED(<columnName>)

Example: Suppose you have two tables:

• Orders: Contains order details including a foreign key CustomerID.
• Customers: Contains customer information, including the CustomerName column.

To add a calculated column in the Orders table that fetches the customer’s name:

Customer Name = RELATED(Customers[CustomerName])

This formula pulls the CustomerName from the Customers table into the Orders table.

Key Points:

• RELATED works with one-to-many or many-to-one relationships.
• Ensure that the relationship between tables is properly established in the model.

---

2. Using RELATEDTABLE in Power BI

What is RELATEDTABLE?

The RELATEDTABLE function retrieves an entire table of related rows from another table. It is often used with aggregation functions like SUM, COUNT, or AVERAGE to calculate metrics across related rows.

Syntax:

RELATEDTABLE(<tableName>)

Example: Suppose you have two tables:

• Orders: Contains order details, including the foreign key CustomerID.
• Customers: Contains customer information.

To calculate the number of orders for each customer in the Customers table:

Order Count = COUNTROWS(RELATEDTABLE(Orders))

This formula returns the count of orders associated with each customer.

Key Points:

• RELATEDTABLE works with one-to-many relationships.
• It returns a table that can be used with aggregation functions.

---

3. Creating an Aggregated Column with RELATEDTABLE

Let’s explore how to use RELATEDTABLE to create a column that aggregates values from a related table.

Scenario: You want to calculate the total order amount for each customer.

Steps:

1. In the Customers table, create a new calculated column.
2. Use RELATEDTABLE to fetch all rows from the Orders table related to each customer.
3. Apply the SUM function to calculate the total order amount.

DAX Formula:

Total Order Amount = SUMX(RELATEDTABLE(Orders), Orders[OrderAmount])

Explanation:

• RELATEDTABLE(Orders) retrieves all rows from the Orders table related to the current customer.
• SUMX iterates through these rows and sums the OrderAmount for each customer.

Result: Each customer in the Customers table will have a calculated column showing their total order amount.

---

4. Best Practices and Tips

• Model Relationships: Ensure relationships between tables are correctly defined in the data model to avoid errors.
• Performance Considerations: When working with large datasets, optimize calculations to improve performance.
• Use Measures for Aggregations: Prefer measures over calculated columns for better flexibility and performance.

---

Conclusion

The RELATED and RELATEDTABLE functions are powerful tools for leveraging relationships in Power BI. They enable you to perform complex lookups and aggregations across tables, enhancing your ability to create insightful reports. By mastering these functions, you can unlock the full potential of your data model.

---