Power BI One-to-One Relationships - When They Help and When They Hurt

Most Power BI relationship advice focuses on one-to-many because that's what you use 95% of the time. Fact tables to dimensions, child tables to parents, the normal star schema setup. Then you hit a situation where two tables have a one-to-one relationship and you start wondering if you've done something wrong.

You probably have. Or rather, you probably should consolidate. One-to-one relationships exist in Power BI but they're almost always a sign that your model should be simpler than it is. Microsoft has a guidance article that lays out the technical scenarios, and it's worth reading. This post is about what to actually do in practice when you encounter them.

The two situations where one-to-one shows up

Microsoft's article describes two scenarios. The first is degenerate dimensions. The second is row data spanning multiple tables. Both deserve different treatment.

A degenerate dimension is when you've got order-level attributes you want to slice by but you don't want them cluttering up your fact table. Order number, order line number, that sort of thing. You create a separate table holding just those attributes plus a key, link it back to the fact table one-to-one, and now report users can filter and group by order without polluting the fact table.

The other scenario is more common and more problematic. You've got a Product table from one source system, and a Product Category table from another source. Both share a SKU. They're really the same business entity, but they came from different places, so you brought them in as two tables. One-to-one relationship between them, job done.

This is where most of the trouble lives.

Why splitting an entity across tables is usually a mistake

When you have one product entity represented across two tables, you've created problems for everyone who uses the model.

Report authors get confused. They look at the Data pane and see "Product" with some fields and "Product Category" with other fields. Are these related? Which one do I use? Why is colour in Product but category in Product Category? The mental model gets fuzzy and reports get inconsistent.

You can't build hierarchies that span the split. If you want a Category > Product hierarchy in a matrix visual, you can't do it because hierarchies need to come from a single table. Workarounds exist (calculated columns, denormalisation in DAX) but they all add complexity.

Worst of all, when the data isn't perfectly aligned between the two tables, you get BLANKs in reports. The Microsoft example shows this clearly. Product SKU CL-02 exists in the Product table but not the Product Category table. So when a report joins them, that row's category is BLANK. Now the report has a hole.

I've inherited Power BI models where someone built a one-to-one relationship for a "logical separation" between attributes from different source systems. The result was always the same: report users got confused, blank values crept into visuals, and someone eventually paid us to clean it up.

The fix is merge in Power Query

Microsoft's recommendation is the right one. When you have a one-to-one intra source group relationship, consolidate into a single table by merging in Power Query.

Here's the process I follow on consulting jobs:

Identify the master query. Of your two related queries, one is usually more "complete" than the other. Pick that as the base. The other one becomes the lookup that gets merged in.

Merge with a left outer join. This keeps every row from your master query and adds matching columns from the secondary query. Rows in the secondary that don't have a match in the master get dropped, which is usually what you want. Rows in the master that don't have a match in the secondary get null values for the merged columns, which you'll handle next.

Expand only the columns you actually need. When the merge brings in the secondary table as a structured column, you expand it to flatten the fields you care about. Don't expand everything. If you've got 15 columns in the secondary table but only need 3, only expand 3. Less clutter, smaller model, faster refresh.

Replace nulls with sensible defaults. Any rows that didn't match in the secondary will show as null. In your reports, null values get rendered as BLANK which looks like a bug to end users. Replace them with something explicit like "[Undefined]" or "Unknown" or whatever fits the domain. This makes filtering and grouping behave predictably.

Disable load on the secondary query. This is the bit people forget. After merging, you don't need the secondary table loaded into your model. Right-click the query, untick "Enable load". It still exists in Power Query as a staging step, but it doesn't take up space in the model and it doesn't show up in the Data pane for report authors.

The end result: one clean Product table with all the attributes, no second table cluttering the model, no BLANK rows in reports, hierarchies work, life is good.

For Power BI consulting work we do, this consolidation pattern comes up on almost every engagement. Existing models are riddled with these split entities because nobody thought about the modelling layer when they imported the data. A few hours of merging and reshaping makes the model dramatically more usable.

When you actually do want the one-to-one relationship

Degenerate dimensions are the main case where one-to-one stays. You've got a fact table with millions of rows and an order number column that's basically unique. You don't want to load that column directly because it'd be expensive to filter on, and you want users to be able to slice by order number and order line number cleanly.

The pattern: create a surrogate key in the fact table that's a deterministic combination of order number and line number (multiply order number by 1000 and add line number, or whatever scheme works for your data). Build a separate dimension table that contains the same surrogate key plus the friendly columns (order number, line number, maybe order date if that helps). Set a one-to-one relationship between them.

Why this works: the dimension table is small (one row per order line, with just a few small columns), so it's cheap to scan. The fact table is big but doesn't carry the friendly columns. Filters on the dimension table propagate to the fact table through the relationship. Storage is reduced, query performance improves, the Data pane is cleaner.

Microsoft's article goes into this in detail and the technique is sound. Worth doing on any fact table that has high-cardinality reference columns you want to filter by.

Cross source group relationships are a different beast

The article also touches on cross source group one-to-one relationships, which is when one of the tables is in DirectQuery mode and the other is imported, or when the two tables are in different DirectQuery sources. These behave differently because the filter propagation has to cross the source boundary.

For practical purposes: avoid them where you can. Composite models in Power BI work, but the performance and behaviour of cross-source one-to-one relationships is harder to reason about than the intra-source variety. If you're in this situation, my advice is usually to bring both tables into the same source (often by importing them) so you can merge them properly in Power Query.

There are legitimate reasons to keep tables in different sources (data sovereignty, refresh frequency, system of record) but the cost is more complex modelling and slower reports. Make the trade-off deliberately rather than ending up there by accident.

The fact-to-fact one-to-one trap

One pattern I see occasionally that almost always indicates a problem: two fact tables related one-to-one. Like a Sales table and a Sales Returns table where every sale has at most one return record, joined on order ID.

This usually means one of two things. Either the two facts are really the same fact and should be combined into a single table with an "is return" flag or a returns columns set, or one of the facts is really a dimension to the other (the returns data is just additional attributes about the original sale).

If you find yourself building one-to-one between two fact tables, pause and ask whether the model is right. Usually the answer is no.

How this fits into broader modelling discipline

One-to-one relationships are a symptom. The deeper issue is whether your data model reflects how the business actually thinks about the data, and whether report authors can navigate it without a map.

The best Power BI models I've seen all have a few things in common. Tables represent clear business entities. Relationships are mostly one-to-many. Hierarchies live within tables and make sense for the domain. Naming is consistent. The Data pane reads almost like documentation.

This doesn't happen by accident. It happens because someone with modelling experience designs the structure before the report-building starts. Most of the Microsoft data and BI work we do for clients ends up touching modelling in some way, even when the brief was just "build us a dashboard". You can't build a good dashboard on a bad model.

If you're inheriting an existing Power BI environment and the models are a mess, consolidating one-to-one relationships is a good first cleanup project. Low risk, high impact, makes everything downstream easier.

Want help with a modelling cleanup or a fresh Power BI build? Get in touch. Most of our Power BI engagements start with a model review because that's where the real value lies.

Reference

Microsoft's full one-to-one relationship guidance: One-to-one relationship guidance. Worth reading for the technical details on relationship evaluation, especially if you're working with composite models.