How to Create a Relationship in Power BI Without Unique Values (Bridge Table Fix)

Our finance lead sent me a budget file and asked for one matrix: sales next to budget for every region and product category. The budget has a row per region and category, and so does the sales table, so neither side has a column with unique values.

Power BI wants unique values on one side of every regular relationship. Drag Region onto Region and it quietly picks many-to-many, which looks fine until the numbers come back wrong.

In this tutorial, we’ll create a relationship in Power BI without unique values the right way. I’ll show the two wrong results I got first, then build small bridge tables with DAX and connect both tables to them, using my 2026 sales and budget demo files.

Quick answer: Don’t connect the two tables to each other. Create a bridge table of unique keys with Regions = DISTINCT(UNION(VALUES(Sales[Region]), VALUES(Budget[Region]))) (Table tools > New table), do the same for any other shared column, then relate each table to the bridge as Many to one (*:1) with a Single cross-filter direction. Use the bridge columns in your visuals.

Why Power BI needs unique values on one side

A relationship has a cardinality: one-to-many, many-to-one, one-to-one or many-to-many. Microsoft defines the “one” side as a column that contains unique values, and the “many” side as one that can repeat. Power BI Desktop checks the data and picks the cardinality for you when you create the relationship. Our Power BI tutorials cover model basics if this is new.

When both columns repeat, you get three options, and only one gives correct numbers:

Option	What happens	My verdict
Many-to-many (*:*) between the two tables	Saves, but filters travel only on that one column	Wrong totals in my test
No relationship	Every row shows the grand total	Wrong
Bridge tables with Many to one (*:1)	Each table filters through a list of unique keys	Correct, and fastest

Many-to-many relationships are also “limited” relationships. They join at query time, RELATED can’t read across them, and Microsoft lists them as slower than one-to-many ones. The cross-filter direction matters too; I explain it in Power BI cross filter direction.

My demo files: sales and a category budget

My demo model already has a Sales table: 60 orders from January to September 2026, $165,701 in total, with Region (East, West, South, Central) and Category (Laptops, Monitors, Accessories, Software) on every row. It also has a region targets table and a sales reps table from earlier posts.

The new file is SPGuides_Category_Budget_2026.csv with three columns: Region, Category and Budget. Load it with Home > Get data > Text/CSV, check the preview and select Load:

Notice that Region repeats four times and each category repeats four times. There are 16 rows, one per region and category. Here they are, so you can recreate the file:

Region	Laptops	Monitors	Accessories	Software	Total
East	$60,000	$10,000	$5,000	$6,000	$81,000
West	$45,000	$4,000	$2,000	$10,000	$61,000
South	$5,000	$8,000	$1,000	$3,000	$17,000
Central	$1,000	$1,000	$2,000	$1,500	$5,500
Total	$111,000	$23,000	$10,000	$20,500	$164,500

I renamed the query to Budget and added one measure, formatted as currency. If you want to check how many distinct values a key column has before you model it, see counting rows and distinct values in Power Query.

Total Budget = SUM(Budget[Budget])

Note: on load, Power BI created Budget (Region) to my targets table on its own, because that table has one row per region. I deleted it in Manage relationships so the test starts clean.

What happens with a many-to-many relationship

The obvious move is Modeling > Manage relationships > New relationship, From table Sales, To table Budget, and Region selected in both previews. The dialog first guessed the Category columns; I clicked Region on both sides instead:

Power BI set the cardinality to Many to many (*:*) and the cross-filter direction to Both, and showed a warning that this cardinality should only be used when neither Region column has unique values and you understand how it behaves.

If you try to force Many to one (*:1) instead, the dialog refuses:

Save stays grayed out, and the message names the first duplicate it found:

Column '' in Table '' contains a duplicate value 'East' and this is not allowed for columns on the one side of a many-to-one relationship or for columns that are used as the primary key of a table.

So I saved it as many-to-many and built a matrix: Sales[Region] on rows, Sales[Category] on columns, Total Sales and Total Budget as values. The totals look right, but the cells don’t:

East shows $81,000 under Accessories, Laptops, Monitors and Software, which is its whole budget. The relationship is on Region only, so Category never filters the Budget table. The row totals happen to be right (East $81,000, South $17,000, West $61,000, Central $5,500), which is exactly why this mistake gets past a quick review.

The column totals give it away: Accessories and Software show $164,500, the full budget, and Laptops shows $159,000 because Central has no laptop sales rows to pass a filter through.

What happens with no relationship at all

Some people delete the relationship and hope for the best. Same matrix, Sales-to-Budget relationship removed:

Every Total Budget cell is $164,500, the grand total, because nothing filters Budget now. Merging the two files into one table is another route, covered in how to merge tables in Power BI, but it mixes two grains in one table, so I keep them separate and add bridge tables instead.

Build the bridge tables with DISTINCT, UNION and VALUES

A bridge (or dimension) table holds each key once. Both fact tables then point at it from their many side, which Power BI handles as a normal one-to-many relationship. Here’s the Regions table:

1. Switch to Table view and select Table tools > New table.
2. Paste the formula below in the formula bar and press Enter.
3. Check the result: one row per region.

Regions = DISTINCT(UNION(VALUES(Sales[Region]), VALUES(Budget[Region])))

VALUES returns the unique regions from each table, UNION stacks the two lists (it keeps duplicates), and DISTINCT removes the repeats. The status bar showed 4 rows: West, East, South and Central. UNION is handy beyond this trick too; see Power BI union two tables.

Taking values from both tables matters. If the budget ever has a region with no sales yet, it still lands in the bridge. Then make the Categories table the same way:

Categories = DISTINCT(UNION(VALUES(Sales[Category]), VALUES(Budget[Category])))

That gives four rows: Laptops, Monitors, Accessories and Software. Because it’s a DAX table, it rebuilds itself on every refresh, so a new region or category in either file shows up without extra work.

Connect both tables to the bridge tables

Open Manage relationships > New relationship again. For Budget to Regions, the dialog picked the Region column on both sides by itself, set Many to one (*:1) and Single, and showed no warning:

Single is the right direction here: Regions filters Budget, not the other way around. The grayed Apply security filter in both directions box only matters for row-level security in Power BI with Both selected.

Create four relationships in total:

1. Budget (Region) to Regions (Region)
2. Budget (Category) to Categories (Category)
3. Sales (Region) to Regions (Region)
4. Sales (Category) to Categories (Category)

I tried Autodetect first. It found only one new relationship, Budget (Region) to the targets table again, and ignored the new bridge tables, so I deleted that one and added the four by hand. Here’s the finished list:

All four new rows are Active and many-to-one, next to the older Sales relationships to the targets and sales reps tables. Important: I left Sales (Region) to the targets table in place. It’s many-to-one with a Single direction, just like the four new ones.

Check the result in the matrix

Now rebuild the matrix from the bridge columns: Regions[Region] on rows, Categories[Category] on columns, and the same two measures. That’s the key step. Fields from the bridge filter both tables; fields from Sales or Budget filter only their own table.

Each cell now has its own budget. East shows Laptops $61,656 against $60,000 and Monitors $9,013 against $10,000. Central shows a $1,000 Laptops and a $1,000 Monitors budget even though it had no sales there, because the bridge lists every region and category.

The row totals (East $81,000, South $17,000, West $61,000, Central $5,500) and the $164,500 grand total match the budget file, and Total Sales is still $165,701. From here a variance measure is easy; my guide on calculating the percentage of two columns in Power BI shows the DIVIDE pattern, and percentage of total by category adds shares.

When a many-to-many relationship is the right choice

Many-to-many isn’t always wrong. Microsoft’s guidance uses it for a higher-grain fact table, such as targets stored per product category while products sit at product level. In that case the relationship goes from the dimension to the fact table, with a Single direction.

Even then, Microsoft recommends measures that return BLANK when someone slices by a lower-level column, because the targets can’t be split correctly. For two fact tables like my sales and budget, the advice is clear: add dimension tables and use one-to-many relationships, which is what we just built.

Common errors and how to fix them

• “contains a duplicate value … not allowed for columns on the one side”: the To column repeats. Point the relationship at a bridge table instead, or check that you picked the right table as the one side.
• Refresh fails after it worked: a duplicate arrived in a column on the one side. Microsoft notes the whole refresh fails. Building the bridge with DISTINCT avoids this for calculated tables.
• Budget repeats in every cell: the visual uses Sales[Category] or Budget[Category] instead of the bridge column, or one of the four relationships is missing.
• A (Blank) row appears: a value in a fact table has no match in the bridge, for example a trailing space or different spelling. Clean the key column so both files use the same text.
• Values don’t match although they look equal: both columns should have the same data type. A number stored as text on one side won’t relate cleanly.

If you only need to show different numbers to different people after this, object-level security in Power BI works on the same model.

Once your model has clean relationships, these Power BI walkthroughs use the same demo data:

• Power BI Month over Month Change [With Examples]
• Power BI Count If True
• Power BI Conditional Formatting Based on Measure
• Power BI Distinct Count with filter [With 5 Examples]

Frequently Asked Questions