Related() and RelatedTable()
RELATED() is one of the most-used DAX functions. You use RELATED() when you’re iterating a table and, within that row context, you need a value from a related table. Its companion, RELATEDTABLE(), goes the opposite way across the relationship. It’s easy to mix these up using RELATED() when it isn’t needed, or forgetting about RELATEDTABLE(). In this article, we’ll cover the typical use cases for both and clear up common misconceptions.
When a row context is active on a table, you can read any column from that same table. What you can’t do is reach columns in other (related) tables directly. That’s exactly when RELATED() or RELATEDTABLE() come into play. We’ll use the model below to demonstrate.
Demo data: Customers (dimension) and Sales (fact), linked on CustomerID.
A.) Customers Table
| CustomerID | CustomerName | Country |
| 1 | Alice | USA |
| 2 | Bob | UK |
| 3 | Charlie | Canada |
B.) Sales Table
| SaleID | CustomerID | Product | Quantity | Amount |
| 101 | 1 | Laptop | 1 | 1000 |
| 102 | 2 | Phone | 2 | 1200 |
| 103 | 1 | Tablet | 1 | 500 |
| 104 | 3 | Laptop | 1 | 1000 |
| 105 | 2 | Monitor | 2 | 400 |
C.) Relationship:
Customers[CustomerID] → Sales[CustomerID] (one-to-many)
1. Using RELATED()
The RELATED() function pulls a column value from the one-side of a relationship into the many-side.
Example: In the Sales Table, we want to display the Country of the customer for each sale.
Calculated Column in Customers Table:
CustomerCountry = RELATED(Customers[Country])Result:
RELATED() works because each Sale row already has a
CustomerID, and Power BI uses that row context to fetch the matchingCountryfrom Customers.
2. Using RELATEDTABLE()
The RELATEDTABLE() function does the opposite. It pulls all rows from the many-side that are related to the current row on the one-side.
Example: In the Customers Table, let’s count how many sales each customer made.
Calculated Column in Sales Table:
TotalSales = COUNTROWS(RELATEDTABLE(Sales))Result:
3. Adding Aggregations with RELATEDTABLE()
We can also aggregate over the related rows. For example, to calculate the total sales amount per customer.
Calculated Column in Sales Table:
TotalSalesAmount = SUMX(RELATEDTABLE(Sales), Sales[Amount])Result:
Step by step for Alice:
- Row context = CustomerID 1
- RELATEDTABLE(Sales) → Sales 101 (1000) and 103 (500)
- SUMX adds them → 1500
Row Context Explanation :
Row context means Power BI evaluates formulas row by row. Whenever you create a calculated column, Power BI automatically has a row context it knows which row it’s on and uses that information.
Here’s how it connects with RELATED() and RELATEDTABLE():
1. Row Context with RELATED() (Many → One)
- In the Sales Table, each row already knows its CustomerID.
- When you write RELATED(Customers[Country]), Power BI looks at the current row, checks the
- CustomerID, and then pulls the matching Country from the Customers Table.
- Example: SaleID 101 has CustomerID = 1. The row context tells Power BI “I’m on Customer 1”, so it fetches USA.
- Without row context, RELATED() wouldn’t know which customer to pick.
2. Row Context with RELATEDTABLE() (One → Many)
- In the Customers Table, each row is one customer.
- When you write RELATEDTABLE(Sales), Power BI takes the current customer (row context) and finds all the rows in Sales where CustomerID matches.
- Example: For CustomerID 2 (Bob), row context says “Filter Sales where CustomerID = 2”. That gives SaleIDs 102 and 105.
- Once RELATEDTABLE() gives you those rows, you can count them (COUNTROWS) or sum them (SUMX).
Row Context Reminder
- RELATED() and RELATEDTABLE() both rely on row context.
- In Sales (many-side), RELATED() works because each row points to a single customer.
- In Customers (one-side), RELATEDTABLE() works because each row filters the Sales table for matching CustomerID.
Conclusion
You can think of RELATED() and RELATEDTABLE() as two sides of the same coin when working with relationships in Power BI. RELATED() is used in a many-to-one relationship to fetch a single matching value from the related table, almost like a lookup. On the other hand, RELATEDTABLE() works in the opposite direction in a one-to-many relationship, it returns all the matching rows from the related table. Together, these two functions let you move across relationships easily, whether you need just one value or an entire set of related records.
