How to Join Two Tables in Power BI Using DAX

Trying to connect two different sets of data in Power BI can feel like you're missing a secret handshake. You have your sales data in one table and your product details in another, and you just want them to talk to each other. This guide will show you how to join tables directly within Power BI using DAX functions, giving you a powerful way to enrich your reports without ever leaving the dashboard.

The Foundation: Relationships Are Your "Joins"

In the worlds of SQL or other data tools, you might be used to explicitly writing a "JOIN" command. In Power BI, the primary way to connect tables is by creating relationships. This visual link tells Power BI how two tables are related so DAX functions can seamlessly pull information from one table to another.

The most common type of relationship is one-to-many. Think of it like this:

• A Products Table: You have one row for each unique product (e.g., "The Classic T-Shirt," "Premium Hoodie").
• A Sales Table: You have many rows that correspond to sales of those products. The "Classic T-Shirt" could appear hundreds of times in your sales records.

To link them, you need a common column, often called a "key." In this case, it might be a ProductID or an SKU. The Products table has one unique ProductID for each product, and the Sales table uses that same ProductID for every sale of that item. Your one-to-many relationship is built on this shared key.

How to Create a Relationship in Power BI

Before any DAX magic can happen, you need to establish this link. Power BI is great at auto-detecting relationships, but it's good practice to know how to do it manually.

1. Navigate to the Model view in Power BI (the third icon on the left-hand pane).
2. You'll see your tables represented as cards. Find the key column in both tables (e.g., ProductID).
3. Click and hold the key column in one table and drag it directly on top of the corresponding key column in the other table.
4. A line will appear connecting the two tables, often with a "1" on the "one" side (Products) and an asterisk (*) on the "many" side (Sales).

With this relationship in place, you’ve basically performed your join. Now you can use DAX to take advantage of it.

Simplest Method: Using the RELATED Function

The RELATED function is your go-to tool for pulling data from the "one" side of a relationship to the "many" side. It’s incredibly useful for bringing descriptive information into a busy transaction table for better analysis.

Let's stick with our Sales and Products tables. Your Sales table has SalesID, ProductID, Quantity, and OrderDate. But what if you want to analyze sales by the product category (e.g., "T-Shirts," "Hoodies," "Accessories")? That information lives in your Products table.

You can use RELATED to create a new calculated column in your Sales table that pulls in the product category.

Step-by-Step Example with RELATED

1. Go to the Data view and select your Sales table.
2. From the "Table tools" tab in the ribbon, select New column.
3. In the formula bar, type the following DAX formula:

Product Category = RELATED(Products[Category])

Let’s break this down:

• Product Category = This is just the name of our new column.
• RELATED(...): This tells Power BI to look across an existing relationship to find a value.
• Products[Category]: This is the column we want to pull. We're telling RELATED to go find the Products table and grab the value from its Category column for the corresponding product.

After you press Enter, a new "Product Category" column will appear in your Sales table. For every single transaction, it will now show the correct category. Now you can easily create visualizations, charts, or other reports that show your sales performance sliced by category, even though that data wasn't originally in your sales data.

For Other Scenarios: Meet the LOOKUPVALUE Function

What if you need to grab a value but there isn't a direct active relationship? Or maybe you need to look something up based on a different set of criteria. That's where LOOKUPVALUE comes in handy. If you’re familiar with VLOOKUP or INDEX/MATCH in Excel, LOOKUPVALUE will feel very familiar.

LOOKUPVALUE lets you retrieve a specific value from a table by matching keys. It scans a column for a value you specify and returns a corresponding value from another column in that same table.

Imagine your company has a separate, disconnected table called MarketingCampaigns with columns for CampaignName and the PromoCode used for that campaign. Your Sales table also has a PromoCode column, but no relationship is set up between them.

You can use LOOKUPVALUE to pull the CampaignName into your Sales table.

Step-by-Step Example with LOOKUPVALUE

1. Go to the Data view and select your Sales table.
2. Click New column in the ribbon.
3. Enter the following formula:

Campaign Name = LOOKUPVALUE(
    MarketingCampaigns[CampaignName], 
    MarketingCampaigns[PromoCode], 
    Sales[PromoCode]
)

Here's how this formula works:

• LOOKUPVALUE(result_column_name, search_column_name, search_value): This is the basic structure.
• MarketingCampaigns[CampaignName]: This is the column containing the value we want to get (the campaign name).
• MarketingCampaigns[PromoCode]: This is the column we're going to search within.
• Sales[PromoCode]: This is the value we're looking for on the current row of our Sales table.

In plain English, the formula is saying: "For each row in the Sales table, take the promo code. Go search for that exact same code in the PromoCode column of the MarketingCampaigns table. When you find it, grab the campaign name from that same row and put it here."

Calculated Columns vs. Measures: A Quick Guide

So far, we've been creating calculated columns. They are great but come with a tradeoff. Here's what you need to know about the two main ways DAX performs calculations.

• Calculated Columns: A calculated column is computed for every single row in your table when you refresh your data. The results are physically stored in your data model, which increases the file size.
• Measures: A measure is calculated on the fly, at the moment you use it in a visualization. It isn't stored in your model and calculates an aggregated value (like a sum, average, or count) based on the context of your report (e.g., date slicers, selected filters).

A good rule of thumb: if you want to put it on a chart axis or in a slicer, it's probably a calculated column. If you want to see a final number in a visual, it should be a measure.

Putting It All Together: From a Lookup to A Dynamic Measure

Let's create a dynamic measure that gives you a key business metric. Imagine we want to calculate our total revenue, but the Sales table only contains Units Sold and ProductID. The Price for each product is in the Products table.

Instead of first creating a calculated column in the Sales table to pull in the price, and then another calculated column to multiply units by price, we can do it all in a single, efficient measure.

Creating a Total Revenue Measure

1. From the Report view, make sure you don't have a specific table selected. In the "Home" tab of the ribbon, click New measure.
2. Enter this powerful DAX formula:

Total Revenue = 
SUMX(
    Sales,
    Sales[Units Sold] * RELATED(Products[Price])
)

What This Measure is Doing:

• Total Revenue = The name of our measure.
• SUMX(...): This is an "iterator" function. It goes through a table row by row, performs a calculation, and then sums up the results.
• Sales,: The first part tells SUMX which table to iterate over - our Sales table.
• Sales[Units Sold] * RELATED(Products[Price]): This is the calculation to perform for each row. It takes the number of units sold on that row and multiplies it by the product's price, which it fetches from the related Products table using our RELATED function.

The result is a single, clean "Total Revenue" measure you can drop into any visual. And because it’s a measure, it will automatically recalculate based on any filters you apply. If you filter your report for "January" or the product category "Hoodies," it will instantly show you the correct total revenue for that specific context. You didn't just join data, you turned it into a flexible, powerful insight.

Final Thoughts

Connecting tables in Power BI moves beyond simple join commands and into a more powerful model of relationships. By establishing these links first and then using DAX functions like RELATED and LOOKUPVALUE in calculated columns or dynamic measures, you're able to build remarkably rich and interactive reports to answer complex business questions.

While mastering DAX is a valuable skill, we know it involves learning functions, contexts, and data modeling concepts that take time and energy. We built Graphed to cut through this complexity. Instead of writing formulas to join sales and product data, you can simply ask, "Show me a dashboard of my total revenue by product category from Shopify for last quarter," and our AI analyst builds the right visualizations for you in seconds, with your data connected in real-time.