Working with RELATED and RELATEDTABLE in Power BI

Course: Microsoft Power BI Training

Topic: Working with RELATED and RELATEDTABLE Functions in DAX

Course Link: Microsoft Power BI Training

Demo Link: Register for a Course Demo

JustAcademy: Visit JustAcademy

1. Introduction to RELATED and RELATEDTABLE

Power BI allows data to be organized into multiple tables instead of storing every piece of information in one large table. For example, a sales dataset may contain separate tables for Customers, Products, Orders, and Sales. These tables can be connected through relationships in the data model.

Data Analysis Expressions (DAX) provides relationship functions that help retrieve information from related tables. Two important functions are RELATED and RELATEDTABLE.

• RELATED: Returns a single value from a related table, usually by moving from the many side of a relationship to the one side.
• RELATEDTABLE: Returns a table containing related rows, commonly by moving from the one side of a relationship to the many side.

These functions are useful for creating calculated columns, performing aggregations, classifying records, and analyzing data across connected tables.

2. Learning Objectives

After completing this topic, you should be able to:

• Understand relationships between tables in Power BI.
• Explain the purpose and syntax of RELATED and RELATEDTABLE.
• Understand the difference between scalar values and table results.
• Use RELATED to retrieve customer, product, and category information.
• Use RELATEDTABLE to analyze related sales or order records.
• Combine RELATEDTABLE with functions such as SUMX, COUNTROWS, and AVERAGEX.
• Understand row context and filter context in DAX.
• Identify common errors and troubleshoot relationship-based calculations.
• Apply both functions to practical business reporting scenarios.

3. Understanding Relationships in Power BI

3.1 What Is a Relationship?

A relationship connects two tables through matching columns. These columns usually contain a common identifier, such as CustomerID, ProductID, or OrderID.

For example, a Customers table may store each customer's name and city, while a Sales table stores individual transactions and the CustomerID associated with each transaction.

3.2 Example Customers Table

3.3 Example Sales Table

3.4 Understanding the One-to-Many Relationship

In this example, one customer can have multiple sales transactions. Each customer appears once in the Customers table, but the same CustomerID can appear multiple times in the Sales table.

• One side: Customers table.
• Many side: Sales table.
• Relationship key: CustomerID.

The relationship allows Power BI to connect a sales transaction with the relevant customer information. RELATED and RELATEDTABLE use this existing relationship to retrieve values or rows.

4. Introduction to the RELATED Function

4.1 What Is RELATED?

The RELATED function retrieves a single value from a column in a related table. It is commonly used when working with a row in a table on the many side and you need information from the corresponding row on the one side.

For example, a Sales table may contain CustomerID but not the customer's name. RELATED can retrieve the name from the Customers table.

4.2 Syntax of RELATED

RELATED(<column>)

Parameter:

• column: The column in the related table whose value you want to retrieve.

Return value: A single scalar value, such as text, a number, a date, or another supported column value.

4.3 Creating a Calculated Column Using RELATED

Suppose you want to add the customer's name to each row of the Sales table.

In Power BI Desktop:

1. Open the Data view or Table view.
2. Select the Sales table.
3. Select New column.
4. Enter the following DAX expression.

Customer Name = RELATED(Customers[CustomerName])

This expression retrieves the customer name corresponding to the CustomerID of the current Sales row, provided that the required relationship exists and the key matches a customer record.

4.4 Expected Result

The new column contains the corresponding customer name for each sales transaction. The customer name is repeated when a customer has multiple sales records.

5. Practical Uses of RELATED

5.1 Retrieving Product Categories

Suppose the Products table contains product names and categories, while Sales contains ProductID and transaction amounts.

Product Category = RELATED(Products[Category])

This calculated column adds the category of each product to the Sales table.

5.2 Retrieving Product Prices

Product List Price = RELATED(Products[ListPrice])

This retrieves the product's list price from the Products table. It does not automatically calculate the actual transaction price or apply discounts. Those calculations require additional DAX logic and appropriate business rules.

5.3 Calculating a Discounted Amount

Assume the Products table contains a DiscountRate column, stored as a decimal such as 0.10 for 10%. A calculated column in Sales can calculate the amount after discount as follows:

Discounted Amount =
Sales[Amount] * (1 - RELATED(Products[DiscountRate]))

For a transaction amount of 1,000 and a discount rate of 10%, the result is 900.

Important: The example assumes the discount rate applies to the transaction amount. In a real model, confirm whether the discount is stored at product, order, or transaction level.

5.4 Retrieving Department Information

In an employee reporting model, the Employees table may contain DepartmentID, while Departments contains the department name.

Department Name = RELATED(Departments[DepartmentName])

This can be used to classify employees by department and build employee-level reports.

5.5 Using RELATED Inside FILTER

RELATED can also be used inside iterator or filtering expressions when a row context is available.

Non-Mumbai Sales =
SUMX(
    FILTER(
        Sales,
        RELATED(Customers[City]) <> "Mumbai"
    ),
    Sales[Amount]
)

This measure iterates over Sales rows, uses RELATED to obtain the corresponding customer's city, excludes transactions belonging to Mumbai customers, and sums the remaining amounts.

6. Introduction to the RELATEDTABLE Function

6.1 What Is RELATEDTABLE?

RELATEDTABLE returns a table containing the rows related to the current row or evaluation context. It is especially useful when you are working with a row on the one side of a relationship and want to analyze its matching rows on the many side.

For example, when working with the Customers table, RELATEDTABLE can return all sales transactions associated with the current customer.

6.2 Syntax of RELATEDTABLE

RELATEDTABLE(<tableName>)

Parameter:

• tableName: The name of an existing related table.

Return value: A table containing the related rows after the applicable context and filters are considered.

RELATEDTABLE does not return a single value by itself. It is often combined with functions such as COUNTROWS, SUMX, or AVERAGEX to calculate a scalar result.

6.3 Counting Sales for Each Customer

To count how many sales transactions belong to each customer, select the Customers table and create a calculated column.

Total Sales Transactions =
COUNTROWS(RELATEDTABLE(Sales))

RELATEDTABLE identifies the matching Sales rows for the current customer, and COUNTROWS counts those rows.

6.4 Expected Result

Amit Sharma has three sales transactions, while Priya Patel and Rahul Verma each have one.

7. Practical Uses of RELATEDTABLE

7.1 Calculating Total Sales per Customer

To calculate the total sales amount associated with each customer, create a calculated column in Customers:

Total Sales Amount =
SUMX(
    RELATEDTABLE(Sales),
    Sales[Amount]
)

SUMX iterates over the related Sales rows and adds their Amount values.

For the sample data, the expected totals are:

7.2 Calculating Average Sales per Customer

Average Sales Amount =
AVERAGEX(
    RELATEDTABLE(Sales),
    Sales[Amount]
)

This calculates the average transaction amount for each customer.

• Amit Sharma: 53,000 divided by 3 = 17,666.67 approximately.
• Priya Patel: 12,000 divided by 1 = 12,000.
• Rahul Verma: 48,000 divided by 1 = 48,000.

In production reports, consider how blank amounts and missing transactions should be handled.

7.3 Identifying Customers with No Sales

RELATEDTABLE can help identify customers who have no matching sales records.

Customer Sales Status =
IF(
    COUNTROWS(RELATEDTABLE(Sales)) = 0,
    "No Sales",
    "Has Sales"
)

This calculated column assigns a status to each customer based on whether related sales rows exist.

7.4 Counting Orders for Each Product

Suppose the Products table is related to the Sales table through ProductID. You can create a calculated column in Products:

Product Transaction Count =
COUNTROWS(RELATEDTABLE(Sales))

This counts the matching sales records for each product. If the Sales table stores multiple lines for one order, the result counts transaction rows rather than distinct orders.

7.5 Calculating Revenue for Each Product

Product Revenue =
SUMX(
    RELATEDTABLE(Sales),
    Sales[Amount]
)

This formula calculates the total amount across the Sales rows related to each product.

8. RELATED vs RELATEDTABLE

Easy way to remember: RELATED brings back one value; RELATEDTABLE brings back related rows.

9. Understanding Row Context

9.1 What Is Row Context?

Row context means that a DAX expression is evaluating a particular row of a table. Calculated columns naturally evaluate row by row. Iterator functions such as SUMX and AVERAGEX also create row context while they iterate over a table.

9.2 Row Context with RELATED

Consider the following calculated column in Sales:

Customer City = RELATED(Customers[City])

For each Sales row, Power BI uses the current row's relationship key to retrieve the matching customer's city.

9.3 Row Context with RELATEDTABLE

Consider the following calculated column in Customers:

Sales Count = COUNTROWS(RELATEDTABLE(Sales))

For each Customers row, RELATEDTABLE evaluates the related Sales rows for that customer, and COUNTROWS counts them.

9.4 Why Context Matters

RELATED requires a row context to identify the current row from which it should retrieve a value. RELATEDTABLE evaluates related rows using the applicable context. If a formula is used in a measure without the necessary row context, it may not behave as expected. Understanding context is essential for writing reliable DAX.

10. Using These Functions in Calculated Columns and Measures

10.1 Calculated Columns

A calculated column is evaluated for each row when the model is processed or refreshed. Its result is stored in the model.

Example calculated column in Sales:

Customer Name = RELATED(Customers[CustomerName])

Example calculated column in Customers:

Sales Count = COUNTROWS(RELATEDTABLE(Sales))

10.2 Measures

A measure calculates results dynamically in response to report filters and visual context. For straightforward sales totals, a normal measure is usually preferable to a calculated column.

Total Sales = SUM(Sales[Amount])

If a report needs the total sales for the customers selected in a visual, relationships in the model may already provide the required filtering, so RELATEDTABLE is not always necessary.

A measure can also use RELATEDTABLE inside an iterator when the expression establishes the required row context. For example:

Sales Across Customer Rows =
SUMX(
    VALUES(Customers[CustomerID]),
    CALCULATE(
        SUMX(
            RELATEDTABLE(Sales),
            Sales[Amount]
        )
    )
)

This illustrates a context-dependent use of RELATEDTABLE within an iterator. For ordinary customer-level sales reporting, the simpler measure SUM(Sales[Amount]) is generally clearer when the model relationship is configured correctly.

Best practice: Do not add RELATED or RELATEDTABLE to every measure automatically. First check whether the existing relationships and filter context already produce the desired result.

11. Setting Up Relationships Before Using the Functions

Both functions rely on relationships in the semantic model. Before writing DAX, verify the model setup.

1. Open Power BI Desktop.
2. Select Model view.
3. Locate the tables you want to connect.
4. Check whether the relevant key columns match.
5. Create a relationship if one does not exist.
6. Confirm the relationship cardinality, such as one-to-many.
7. Check which relationship is active.
8. Validate the result with a small table visual before using it in a report.

For a typical star schema, dimension tables such as Customers and Products are on the one side, while a fact table such as Sales is on the many side.

12. Common Errors and Troubleshooting

12.1 Error: No Relationship Exists

Problem: RELATED or RELATEDTABLE cannot find the expected related data because the model does not contain an appropriate relationship.

Solution: Create or correct the relationship using matching key columns. Ensure the key on the one side is unique when the model requires a one-to-many relationship.

12.2 Error: Using RELATED in the Wrong Direction

Problem: You are working on the Customers table and attempt to retrieve a single value from Sales using RELATED, even though one customer can have multiple sales rows.

Solution: Use RELATEDTABLE to obtain the related sales rows, then aggregate them with COUNTROWS, SUMX, or another suitable function.

12.3 Duplicate Keys on the One Side

Problem: The supposed dimension table contains duplicate values in the key column, preventing the intended one-to-many relationship.

Solution: Clean the data, remove unintended duplicates, or redesign the model. Do not arbitrarily delete records if the duplicates represent legitimate business entities.

12.4 Blank Results from RELATED

Problem: A related value appears blank.

Possible causes:

• The key in the current row has no matching row in the related table.
• One or more key values are blank.
• Data types or formatting differ between key columns.
• The relationship is inactive or incorrectly configured.
• The requested column itself contains blank values.

Solution: Check the key values, data types, relationship status, and source data. Remember that unmatched keys can result in blank related values.

12.5 Unexpected Counts from RELATEDTABLE

Problem: The count is higher than the number of distinct business orders you expected.

Cause: COUNTROWS counts rows, not necessarily unique orders or customers.

Solution: If the Sales table contains multiple rows per order, use a distinct-count calculation over the order identifier where appropriate. For example:

Distinct Orders =
DISTINCTCOUNT(Sales[OrderID])

Choose the aggregation based on the business question rather than assuming each table row represents a unique order.

12.6 DirectQuery Limitations

Some DAX functions have restrictions in DirectQuery mode, especially when used in calculated columns or row-level security rules. RELATEDTABLE is not supported in DirectQuery calculated columns or RLS rules. Check the function documentation and model storage mode before relying on a formula.

13. Best Practices for RELATED and RELATEDTABLE

• Build a clear model with appropriate dimension and fact tables.
• Use unique keys on the one side of one-to-many relationships.
• Use RELATED when you need one value from a related table.
• Use RELATEDTABLE when you need matching rows from a related table.
• Combine RELATEDTABLE with an appropriate aggregation function when a scalar result is required.
• Use measures for calculations that should respond dynamically to report filters.
• Avoid adding large numbers of calculated columns when a measure or model redesign would be more efficient.
• Use descriptive names for calculated columns and measures.
• Validate formulas with sample records and expected results.
• Check the impact of blanks, duplicate keys, and unmatched relationships.
• Review storage-mode limitations before deploying a report.

14. Practical Project: Customer Sales Analysis

14.1 Project Objective

Create a Power BI report that shows customer names, cities, transaction counts, and total sales using RELATED and RELATEDTABLE.

14.2 Required Tables

Customers

Sales

14.3 Step 1: Create the Relationship

Create a one-to-many relationship between Customers[CustomerID] and Sales[CustomerID]. Customers should be on the one side, and Sales should be on the many side.

14.4 Step 2: Add Customer Name to Sales

Customer Name = RELATED(Customers[CustomerName])

This provides a customer name for each transaction.

14.5 Step 3: Count Sales for Each Customer

Create this calculated column in Customers:

Sales Count = COUNTROWS(RELATEDTABLE(Sales))

14.6 Step 4: Calculate Customer Revenue

Customer Revenue =
SUMX(
    RELATEDTABLE(Sales),
    Sales[Amount]
)

14.7 Step 5: Create a Report Visual

Add a table visual containing:

• Customers[CustomerName]
• Customers[City]
• Customers[Sales Count]
• Customers[Customer Revenue]

14.8 Expected Output

This project demonstrates how RELATED enriches transaction records with customer details, while RELATEDTABLE helps aggregate transactions for each customer.

15. Practice Exercises

Exercise 1: Retrieve a Product Category

A Sales table contains ProductID, and a Products table contains ProductID and Category. Write a calculated column that retrieves the category for every sales row.

Expected formula:

Category = RELATED(Products[Category])

Exercise 2: Count Orders by Customer

Create a calculated column in Customers that counts related rows in the Orders table.

Expected formula:

Order Count = COUNTROWS(RELATEDTABLE(Orders))

Exercise 3: Calculate Revenue by Product

Assume Products is related to Sales. Create a calculated column in Products that sums the Amount column for related sales rows.

Expected formula:

Product Revenue =
SUMX(
    RELATEDTABLE(Sales),
    Sales[Amount]
)

Exercise 4: Identify Products Never Sold

Create a calculated column in Products that labels each product as Sold or Never Sold.

Expected formula:

Sales Status =
IF(
    COUNTROWS(RELATEDTABLE(Sales)) = 0,
    "Never Sold",
    "Sold"
)

Exercise 5: Compare RELATED and RELATEDTABLE

Explain why RELATED is appropriate for retrieving a customer's name in a Sales row, while RELATEDTABLE is appropriate for counting all sales rows for a customer.

Expected answer: RELATED returns one value from a related table; RELATEDTABLE returns matching rows that can be counted or aggregated.

16. Interview Questions and Answers

Q1. What is the RELATED function in Power BI?

RELATED is a DAX function that returns a single value from a related table, commonly from the one side of a relationship when evaluating a row on the many side.

Q2. What is the RELATEDTABLE function?

RELATEDTABLE returns a table of rows related to the current row or evaluation context.

Q3. What is the main difference between RELATED and RELATEDTABLE?

RELATED returns a scalar value, while RELATEDTABLE returns a table of related rows.

Q4. Do these functions require relationships between tables?

Yes. They depend on an appropriate existing relationship in the data model.

Q5. Can RELATED retrieve a customer's name for every sales transaction?

Yes. When Sales is on the many side and Customers is on the one side, a calculated column such as RELATED(Customers[CustomerName]) can retrieve the matching customer name.

Q6. Can RELATEDTABLE calculate the number of orders?

RELATEDTABLE returns the related order rows. COUNTROWS can count those rows, assuming the relationship and table grain match the intended definition of an order.

Q7. Why is RELATED commonly used in calculated columns?

Calculated columns provide a row context, which allows RELATED to identify the current row and retrieve the corresponding value from a related table.

Q8. Can RELATEDTABLE return a scalar value directly?

No. RELATEDTABLE returns a table. Use an aggregation or table-processing function such as COUNTROWS or SUMX when a scalar result is needed.

Q9. What is the role of SUMX with RELATEDTABLE?

RELATEDTABLE identifies the matching rows, and SUMX iterates through those rows to evaluate and sum an expression, such as Sales[Amount].

Q10. What should you check when RELATED returns blanks?

Check key matching, blank values, data types, relationship configuration, and whether the requested related value itself is blank.

Q11. Can RELATED be used inside FILTER?

Yes. FILTER iterates over rows and creates row context, allowing RELATED to retrieve a related value for each row being evaluated.

Q12. Is RELATEDTABLE supported in DirectQuery calculated columns?

No. RELATEDTABLE has a documented restriction for DirectQuery calculated columns and row-level security rules. Always confirm the limitations for the model's storage mode.

17. Quick Revision Notes

• RELATED: Returns one related column value.
• RELATEDTABLE: Returns related rows as a table.
• RELATED example: RELATED(Customers[CustomerName])
• RELATEDTABLE example: RELATEDTABLE(Sales)
• Count related rows: COUNTROWS(RELATEDTABLE(Sales))
• Sum related amounts: SUMX(RELATEDTABLE(Sales), Sales[Amount])
• Relationship requirement: Both functions rely on an appropriate relationship.
• RELATED context: Requires row context to identify the current row.
• RELATEDTABLE result: Must be processed or aggregated when a scalar output is needed.
• Model design: Use unique keys on the one side of a one-to-many relationship.

18. Conclusion

RELATED and RELATEDTABLE are important DAX relationship functions for working with connected tables in Power BI. RELATED retrieves a single value, such as a customer's name or a product category, while RELATEDTABLE returns matching rows that can be counted, summed, or analyzed.

Understanding the relationship direction, row context, data model, and return type helps you choose the correct function and avoid common calculation errors. Practice both functions with Customers, Products, Orders, and Sales tables to strengthen your DAX skills and create more effective Power BI reports.

Continue learning: Explore Microsoft Power BI Training | Register for a Course Demo | JustAcademy Website