How to Create a Relationship in Power BI Without Unique Values

One of the most common roadblocks Power BI users hit is the dreaded message: "You can't create a relationship... because one of the columns must have unique values." This error stops you dead in your tracks, but it doesn't have to be the end of your analysis. This article will show you exactly how to fix this issue and build the relationships you need, even when your data isn't perfect.

Graphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Build Your First Agent

Why Power BI Insists on Unique Values

To understand the solution, it's helpful to first understand the problem from Power BI's perspective. When you create a relationship between two tables, you're telling Power BI how to filter data across them. The most common and efficient way to do this is with a one-to-many relationship.

Think of it like this:

• The "One" Side (usually a lookup table): This table contains a list of unique items, like a list of all the products you sell. Each product ID should appear only once. This is your definitive source of truth for products. Let's call it a Product Details table.
• The "Many" Side (usually a fact or data table): This table contains your transactional data, like a list of every sale you've ever made. The same product ID can appear many times because you can sell the same product over and over. Let's call this a Sales Data table.

When you create a relationship, Power BI uses the unique ProductID from the "one" side (Product Details) to filter the Sales Data on the "many" side. If you click on "Product A" in a chart, Power BI knows to only show you sales records that match that unique product ID.

The error happens when the table that is supposed to be your "one" side has duplicate values. If your Product Details table accidentally listed the same ProductID twice, Power BI gets confused. It wouldn’t know which of the duplicate "Product A" entries to use for filtering. Is it the first one or the second one? To avoid ambiguity and incorrect calculations, it simply prevents the relationship from being made.

AI Agents for Marketing

Crash Course

Graphed

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Download the PDF

Finding and Fixing the Root Problem

The error almost always points to an issue in your lookup table (the "one" side). Real-world data is often messy. You might have duplicates in your lookup table for several reasons:

• Data Entry Errors: Someone might have manually entered the same customer or product twice.
• System Exports: The system you exported data from might have generated duplicate or redundant records.
• Changing Product Info: You might have different rows for the same product ID because its price or description changed over time.

Simply deleting rows isn't always the answer. The key is to manage these duplicates programmatically so your Power BI model is stable and accurate. Here are the three best methods to solve this.

Method 1: Create a Unique "Bridge" Table (Recommended)

This is the most reliable and scalable solution for nearly every scenario. Instead of trying to clean up your messy original table, you create a new, perfectly clean table in Power Query to act as the "one" side of your relationship. This new table serves as a bridge, connecting to both of your original tables.

Let's stick with our Product Details and Sales Data tables, where Product Details has duplicate ProductIDs.

Step-by-Step Instructions:

1. Open Power Query: In Power BI Desktop, click on "Transform data" in the Home ribbon to launch the Power Query Editor.
2. Duplicate Your Lookup Table: In the Queries pane on the left, find your table with the duplicates (e.g., Product Details). Right-click on it and select "Duplicate".
3. Rename the New Table: It's good practice to rename this new query to something clear. Let's call it Product Lookup. You now have an exact copy of your table to work with.
4. Isolate the Key Column: In the Product Lookup query, select the column you want to use for the relationship (e.g., ProductID). Right-click the column header and choose "Remove Other Columns". All columns except for your key will disappear.
5. Remove Duplicates: With the ProductID column still selected, go to the "Home" tab in the ribbon. Click on "Remove Rows" and then select "Remove Duplicates". Power Query will instantly remove all duplicate ProductIDs, leaving you with a perfectly unique list.
6. Close & Apply: Click "Close & Apply" in the top-left corner to load your new table into the Power BI model.
7. Create the New Relationships: Go to the "Model" view in Power BI (the third icon on the left sidebar). You'll see your new Product Lookup table.

• Delete the faulty, inactive relationship between your original tables if it exists.
• Drag the ProductID from your new Product Lookup table to the ProductID in your original Product Details table.
• Drag the same ProductID from Product Lookup over to the ProductID in your Sales Data table.

You’ve now established a proper star schema. Your Product Lookup table is the "one" side for both of the other tables. When you use ProductID from this new lookup table in a slicer or chart, it will correctly filter both your product details and your sales data.

Graphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Build Your First Agent

Method 2: Create a Composite Key by Merging Columns

Sometimes, a single column is not unique, but a combination of columns is. For example, in an event attendance list, Attendee ID might not be unique (the same person can attend multiple events), but the combination of Attendee ID and Event ID will be unique.

In these cases, you can create a new, unique key column in Power Query by merging the columns together.

Step-by-Step Instructions:

1. Open Power Query: Click on "Transform data" to get back to the Power Query Editor.
2. Select Columns to Merge: Select the table you want to work on. Hold down the CTRL key and click to select the two (or more) columns that will create a unique combination (e.g., Attendee ID and Event ID).
3. Merge the Columns: Go to the "Add Column" tab in the ribbon. Click the "Merge Columns" button.
4. Configure the Merged Column: A dialog box will pop up.

• Choose a separator. A dash (-) or colon (:) works well. This is just for readability.
• Give your new column a descriptive name, like UniqueAttendanceID.
• Click "OK".

1. Repeat if Necessary: If you need to create this relationship with another table, repeat the exact same steps in that table to create a matching composite key.
2. Close & Apply: Load the changes back into your model.
3. Create the Relationship: In the Model view, you can now drag your new composite key column (e.g., UniqueAttendanceID) from one table to the other to form a valid one-to-one or one-to-many relationship.

Method 3: Configure a Many-to-Many Relationship (Use With Caution)

Power BI does allow for many-to-many relationships, where it knows that both tables in the relationship contain duplicate values. You might think this is an easy fix, but it should be your very last resort.

Why be cautious? Many-to-many relationships can be ambiguous and can lead to incorrect results if you don't fully understand your data model. They can also significantly slow down the performance of your report.

A legitimate case for this might be relating a Student Demographics table to a Class Roster table. Each contains a StudentID list, and both lists will understandably have duplicates if students change majors or enroll in multiple classes a semester. In most cases, the preferred approach is still Method 1, creating a unique Student ID lookup table. But if you must proceed, this is how you configure it:

AI Agents for Marketing

Crash Course

Graphed

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Download the PDF

How to Set It Up:

1. Attempt the Relationship: In the Model view, drag the column from the first table to the corresponding column in the second table.
2. Edit the Relationship: The "Create relationship" dialog box will appear again.

• Near the bottom, you'll see a dropdown for "Cardinality".
• Select "Many to many (:)". Power BI will show a warning about this type of relationship. Read it and make sure you understand the implications for your dataset.
• Set the "Cross filter direction" as needed (often "Both").
• Click 'OK' to connect.

Again, this approach hides data problems rather than solving them. Method 1 (creating a dedicated lookup/bridge table) is almost always the more stable, efficient, and reliable solution for long-term use.

Final Thoughts

Hitting Power BI's "unique values" error is a common part of the data modeling process. Instead of seeing it as a wall, view it as a hint that your data model can be stronger. By using Power Query to create a dedicated lookup table or a composite key, you take control of your data and build a robust, accurate, and fast report.

If you find that setting up these kinds of data models and dealing with relationships in BI tools still feels cumbersome, you’re not alone. It’s one of the reasons we created Graphed. We wanted to eliminate the technical steps completely. You just connect your data platforms, like Google Analytics or Shopify, and then ask questions in plain English. The relationships, data modeling, and dashboard creation all get handled automatically in the background, giving you live insights in seconds.