How to Get Value from Another Table in Power BI

Trying to pull a value from one table into another is a classic challenge in Power BI, but solving it is a fundamental skill for building powerful reports. If you've ever had a sales table with a ProductID and wished you could just pull in the actual Product Name or Price from a separate products table, you're in the right place. This guide will walk you through the two essential DAX functions for this job: RELATED and LOOKUPVALUE.

The Foundation: Why Table Relationships Matter

Before jumping into formulas, it's critical to understand the concept that makes Power BI so powerful: data modeling and relationships. Unlike a flat Excel sheet, Power BI is designed to work with multiple tables that relate to each other, making it far more efficient and scalable.

Imagine you have two tables:

A Sales table with millions of rows, one for each transaction. It has SaleID, SaleDate, Quantity, and ProductID.
A Products table with a few hundred rows, one for each product you sell. It has ProductID, Product Name, Category, and Price.

You wouldn't want to copy and paste the product name, category, and price into every single one of those millions of sales rows. That would create a massive, bloated file. Instead, we create a relationship between the two tables using their common column: ProductID.

What is a Relationship?

A relationship tells Power BI how a column in one table corresponds to a column in another. In our example, the ProductID in the Sales table connects to the ProductID in the Products table. This is typically a "many-to-one" relationship, as one product can appear in many sales transactions.

Setting this up in Power BI’s "Model" view is what enables the magic. This connection is the highway that allows functions like RELATED to travel between tables and retrieve data.

Without a proper relationship, your tables are just isolated islands of data, and getting them to talk to each other becomes much harder. For most scenarios, getting your relationships right is 90% of the battle.

Method 1: Using the RELATED Function (The Easy Way)

The RELATED function is your go-to tool for this task and should always be your first choice if your data model is correctly set up. It's simple, fast, and efficient.

Think of it as looking up a value “up the chain.” It follows an existing, active relationship from the “many” side of a table to the “one” side to fetch a value.

When to Use RELATED

You have an active, existing relationship between two tables.
You are creating a calculated column on the “many” side (e.g., your Sales table).
You need to bring a value from the “one” side (e.g., your Products table) into the “many” side.

Step-by-Step Example with RELATED

Let's continue with our Sales and Products tables. We have them loaded in Power BI and have already created a many-to-one relationship based on ProductID.

Our goal is to create a new column in our Sales table called Product Name which pulls the name from the Products table.

Step 1: Navigate to the Data ViewIn Power BI Desktop, click on the "Data" icon on the left-hand navigation pane to see your tables.

Step 2: Select Your Table and Create a New ColumnSelect the Sales table from the Fields pane on the right. Then, from the "Table tools" ribbon menu at the top, click "New column".

Step 3: Write the DAX Formula

Product Name = RELATED(Products[Product Name])

Let’s break this down:

Product Name =: This is the name we’re giving our new column.
RELATED(...): This is the function we’re using.
Products[Product Name]: This is the column we want to retrieve. We specify the table name (Products) and then the column name within square brackets ([Product Name]).

Step 4: Press EnterAfter you press Enter, Power BI automatically follows the relationship from the Sales table to the Products table, finds the matching ProductID for each sale, and populates your new column with the corresponding Product Name. It's that simple.

You can use the same logic to bring in the price:

Unit Price = RELATED(Products[Unit Price])

Now you can easily calculate total revenue for each sale by multiplying Quantity by Unit Price, something that was impossible without bringing the price data into your sales table.

Method 2: LOOKUPVALUE (The Flexible VLOOKUP of Power BI)

What if you don't have a relationship between your tables? Or what if your relationship is inactive? This is where the LOOKUPVALUE function comes in. It acts like the familiar VLOOKUP or XLOOKUP in Excel, allowing you to fetch a value without relying on a pre-defined relationship.

When to Use LOOKUPVALUE

When there is no active relationship between your tables.
When the relationship is complex or many-to-many, and RELATED won’t work.
When you need to look up a value based on multiple criteria.
As a last resort when a proper relationship cannot be created (though you should always strive to build a good model first!).

Understanding the LOOKUPVALUE Syntax

The syntax for LOOKUPVALUE is a bit longer than RELATED, but it gives you more control:

Here’s what each part means:

Result_ColumnName: The column containing the value you want to return (e.g., Products[Product Name]).
Search_ColumnName: The column you want to search through in the lookup table (e.g., Products[ProductID]).
Search_Value: The value you are searching for. This is usually the column from your current table that provides the match (e.g., Sales[ProductID]).

Important Note: For LOOKUPVALUE to work correctly, the Search_ColumnName (Products[ProductID] in our case) must contain unique values. If it finds multiple matches for your search value, it will return an error.

Step-by-Step Example with LOOKUPVALUE

Let's assume for this example that we've deleted the relationship between our Sales and Products tables.

Step 1 & 2: Create a New Column in the Sales TableFollow the same initial steps as before. Navigate to the Data view, select the Sales table, and click "New column".

Step 3: Write the DAX Formula

Product Name (Lookup) =LOOKUPVALUE(Products[Product Name],Products[ProductID],Sales[ProductID])

Here’s the plain-English translation of this formula: “Go look in the Products table for the value in the Product Name column. To find the right row, match the value from the ProductID column in the Products table with the value from the ProductID column in the current row of my Sales table.”

When you press Enter, Power BI will perform this lookup for every single row in your Sales table and populate the new column, even without a formal relationship.

RELATED vs. LOOKUPVALUE: Which One is Right for You?

So, which function should you use? The answer almost always depends on the state of your data model.

Go with RELATED when:

You have a clean data model with well-defined relationships.
Performance is a priority. RELATED leverages Power BI’s engine and is generally faster.
Your needs are straightforward: getting a value from a related "one" table.

Turn to LOOKUPVALUE when:

RELATED fails because there is no direct relationship.
You need to perform a lookup in a disconnected or bridging table.
You need custom logic that a simple relationship can't handle.

Best Practice: Always start by trying RELATED. If it works, you know your data model is sound. If it fails, that’s often a signal that you should review your relationships first. Resort to LOOKUPVALUE when a relationship is intentionally absent or simply not possible for your use case.

Common Mistakes to Avoid

As you work with these functions, you might run into a couple of common issues:

"A single value for column...cannot be determined.": This classic error with RELATED almost always means your relationship isn't set up correctly or isn't active. Check the Model view.
Performance Slowdown: If you use LOOKUPVALUE on tables with millions of rows, you might notice your report refreshing slower. This is because it has to perform a search row by row, unlike RELATED which leverages pre-calculated relationships.
Incorrect Relationship Direction: Make sure your cross-filter direction is appropriate. Usually, filtering from the "one" side (Products) to the "many" side (Sales) is the correct and default setup.

Final Thoughts

Bringing values from one table to another is a cornerstone of Power BI development. By mastering both RELATED and LOOKUPVALUE, you give yourself the tools to handle almost any data modeling situation. Strive for building a solid data model with clear relationships, allowing the simple and powerful RELATED function to do most of the heavy lifting.

Manually writing DAX and setting up data relationships is a core skill for any analyst, but it does take time to learn the nuances. At Graphed, we’ve created a more direct path to getting answers from your data. Instead of learning functions, you can connect your data sources and simply ask in plain English, "Show me my sales revenue by product name." Our AI analyst builds the model and relationships for you in the background and instantly generates the chart you asked for, letting you skip the formulas and get straight to the insights.