How to Use RELATED Function in Power BI

Pulling data from one table into another is a common task in any analysis, but in Power BI, it can feel intimidating if you're new to the world of data models and DAX formulas. If you've ever found yourself with sales data in one table and product details in another, you know the struggle of trying to combine them. This is precisely where the RELATED function comes in, acting as your go-to tool for bridging that gap.

This tutorial will walk you through exactly how and when to use the RELATED function in Power BI. We'll cover what it does, show a practical example of how to use it for calculations, and discuss common errors you might encounter along the way.

What Exactly is the Power BI RELATED Function?

Think of RELATED as a smarter, more efficient version of VLOOKUP from Excel, but designed specifically for data models. Its job is simple: it fetches a single value from another table, provided there's an existing relationship between the two tables. When you have a Sales table and a Products table, RELATED is the function that lets you look up the product name or price from the Products table and use it directly within your Sales table.

The key here is the relationship. The RELATED function doesn't work in a vacuum, it follows the path you've already defined in Power BI's Model View. It can only travel from the "many" side of a relationship to the "one" side.

Many Side: Typically a "fact" table, like your Sales table, where the same product ID can appear many times.
One Side: Typically a "dimension" or "lookup" table, like your Products table, where each product ID appears only once.

Because you're fetching data from the unique ("one") side, Power BI knows exactly which value to return for each row on the "many" side. You can't use RELATED to go from the "one" side to the "many" side because Power BI wouldn't know which of the many possible values to pick.

The Syntax

The beauty of RELATED is its simplicity. The syntax requires just one argument:

RELATED(<ColumnName>)

You simply tell it which column you want to retrieve from the related table on the "one" side. That's it.

When and Why Should You Use RELATED?

The RELATED function is most commonly used when creating calculated columns. It helps you "flatten" or denormalize your data, bringing useful information into a single table so you can perform calculations or use it in visuals more easily.

1. To Create Powerful Calculated Columns

This is the primary use case. Imagine your Sales table contains ProductID and QuantitySold, but not the Price. The Price lives in your Products table. To calculate the total revenue for each sales transaction, you need the price on each row of the Sales table. RELATED allows you to pull the Price from Products into a new calculated column in Sales. Then, you can simply multiply QuantitySold by the new Price column.

2. To Add Context for Slicers and Filters

Let's say you want to build a report and filter your sales data by ProductCategory. The category information is in your Products table, not your Sales table. You could create a new calculated column in your Sales table using RELATED(Products[ProductCategory]). Now you have a ProductCategory column right in your Sales table, which you can easily drag into a slicer or use on a chart axis to analyze your sales by category.

3. To Simplify Your DAX Measures

Sometimes, bringing over a related value into a calculated column can make your DAX measures less complex. Instead of nesting multiple functions inside a measure to perform a lookup, you can do the lookup once in a calculated column. This can make your formulas easier to read and debug, especially for teams that are just starting with DAX.

A Practical Walkthrough: Calculating Line Totals in a Sales Table

Let's get practical. Let's assume we have two tables in our Power BI model:

A Sales table with columns: OrderID, ProductKey, and QuantitySold.
A Products table with columns: ProductKey, ProductName, and UnitPrice.

Our Goal: We want to calculate the total revenue for each line in the Sales table. To do this, we need to multiply QuantitySold by the UnitPrice, but the UnitPrice is in the Products table.

Step 1: Verify the Relationship

Before writing any DAX, you must ensure a proper relationship exists. Go to the Model View in Power BI. You should see a line connecting your Sales table and your Products table. Hover over the line to see which columns are connected (it should be ProductKey in both) and confirm the cardinality is one-to-many (1 on the Products side, * on the Sales side).

If there's no relationship, drag the ProductKey column from the Sales table and drop it onto the ProductKey column in the Products table to create it. RELATED cannot work without this step.

Step 2: Create a New Calculated Column to Fetch the Unit Price

Now, let's pull the UnitPrice into the Sales table.

Go to the Data View in Power BI.
Select the Sales table from the Fields pane on the right.
From the Table Tools ribbon at the top, click New Column.
Your DAX formula bar will appear. Enter the following formula:

Unit Price = RELATED(Products[UnitPrice])

Press Enter. Power BI will look at each row in the Sales table, follow the relationship back to the Products table using ProductKey, find the matching UnitPrice, and return it. You've successfully added the price to every sale!

Step 3: Calculate the Line Total

Now that the price is in our Sales table, the final calculation is straightforward. Create another new calculated column:

While still in the Sales table, click New Column again.
Enter this formula:

Line Total = Sales[QuantitySold] * Sales[Unit Price]

Press Enter. And just like that, you have a new column showing the total revenue for each transaction. You can now use this Line Total column to build summaries, charts, and other visuals to analyze your revenue.

Common `RELATED` Function Errors and How to Solve Them

Like any function, RELATED can sometimes throw errors. The most common one you'll encounter by far looks something like this:

"The column 'TableName[ColumnName]' either doesn't exist or doesn't have a relationship to any table available in the current context."

"A single value for column '[ColumnName]' in table '[TableName]' cannot be determined."

Both messages point to the same few problems. If you see this, here's your troubleshooting checklist:

Check Your Relationship (Again): This is the culprit 90% of the time. Go back to the Model View. Is there truly a single, active, one-to-many relationship between the two tables? Is it going in the right direction (i.e., you're creating the column on the "many" side)?
Verify Uniqueness on the "One" Side: The lookup column on your "one" side must be unique. If you have duplicate ProductKey values in your Products table, Power BI won't know which UnitPrice to return, leading to an error. Remove any duplicates from your dimension/lookup tables.
Blank or Mismatched Keys: Check for values in the "many" side's key column (e.g., Sales[ProductKey]) that don't exist in the "one" side's key column (Products[ProductKey]). A well-structured model should prevent this, but it can happen with messy data.

`RELATED` vs. `LOOKUPVALUE`

Another function you might come across is LOOKUPVALUE. It accomplishes a similar goal but works differently. Here's the key distinction:

RELATED: Relies on an existing physical relationship between tables in your data model. It's fast, efficient, and should be your first choice if the relationship exists.
LOOKUPVALUE: Does not require a pre-defined relationship. You specify the columns to match directly within the function, similar to how VLOOKUP or INDEX/MATCH works in Excel. It's more flexible but generally less performant than RELATED.

As a best practice, always try to use RELATED first. Using a properly designed data model with clear relationships is the foundation of a good Power BI report. LOOKUPVALUE is best reserved for situations where creating a formal relationship isn't feasible or could create ambiguity in your model.

Final Thoughts

The RELATED function is a fundamental building block in DAX, enabling you to bring your data model to life by combining information across tables. By mastering it, you can create richer calculated columns, simplify your analysis, and build far more insightful reports without having to manually merge files before loading them into Power BI.

Learning DAX functions like RELATED is a valuable skill, but wrangling data inside sophisticated BI tools is often the kind of manual work that drains time for marketing and sales teams. Instead of spending hours defining relationships and writing formulas to answer basic performance questions, much of this can now be automated. That's why we built Graphed, we wanted a way to let teams ask questions like "Show me my sales revenue by product category" in plain English and instantly get a live dashboard. It's about empowering everyone to move from data to insights in seconds, not hours.