Back to Blog

Power BI Model Relationships - Getting Them Right in Practice

March 24, 2026•9 min read•Michael Ridland

If your Power BI report is showing wrong numbers, there's about a 60% chance the problem is in your relationships. Not your DAX. Not your data. The relationships between your tables. I don't have a scientific study to back up that percentage, but after years of debugging other people's Power BI models, it feels about right.

Model relationships are how Power BI propagates filters from one table to another. Click a slicer, and that filter travels along relationship paths to reach your fact tables. Simple concept, but the details matter enormously. Microsoft's official documentation on model relationships covers the theory thoroughly. Here's what the theory looks like when it hits real projects.

How Filter Propagation Actually Works

Think of your data model as a network of pipes. When someone selects "2025" in a Year slicer, that filter flows through the pipe (relationship) from the Year table to the Sales table, removing any rows that don't match. If the Product table also has a relationship to Sales, and someone selects "Widget A" in a Product slicer, that filter flows through its own pipe to Sales. Both filters apply simultaneously as an AND operation - you get sales of Widget A in 2025.

This is intuitive when you have two or three tables. It gets complicated fast when you have fifteen tables with relationships going in different directions, some active and some inactive, with bi-directional filtering thrown in because someone thought it would fix a problem (it usually creates more problems).

The single most important thing to understand: filters flow from the "one" side to the "many" side by default. A Product dimension table (where each product appears once) filters a Sales fact table (where each product can appear many times). Not the other way around. When you need it to go the other way, you can enable bi-directional filtering, but do it with your eyes open.

Star Schema - Why It Matters More Than You Think

The best thing you can do for your Power BI model is design it as a star schema. Dimension tables around the outside, fact table (or tables) in the middle, one-to-many relationships flowing from dimensions to facts. It's data modelling 101, but you'd be surprised how many Power BI models we encounter that look more like a tangled spider web than a star.

Why does star schema work so well with Power BI? Because it makes filter propagation predictable. Dimension tables filter fact tables. Period. You don't need to think about which direction filters are flowing because there's only one sensible direction. Your DAX gets simpler because RELATED and RELATEDTABLE work naturally along those paths.

We worked with a government agency in Brisbane that had a Power BI model with 23 tables and relationships going in every direction. Some tables were acting as both dimensions and facts. There were bi-directional relationships everywhere because the original developer kept enabling them to "fix" measures that weren't calculating correctly. The model was slow, the numbers were unreliable, and nobody could figure out why some filters affected certain visuals and not others.

We spent two weeks restructuring it into a proper star schema. Twelve dimension tables, two fact tables, all relationships one-to-many flowing from dimensions to facts. The report went from taking 8 seconds to render to under 2 seconds, and - more importantly - the numbers were actually right.

Cardinality Types and When to Use Each

Power BI supports four cardinality types. In practice, you'll use one of them 95% of the time.

One-to-Many (the Default, and Usually Correct)

This is your bread and butter. One product in the Product table relates to many sales in the Sales table. One customer in the Customer table relates to many orders in the Orders table. If you're building a star schema, virtually every relationship will be one-to-many.

Power BI auto-detects cardinality when you create relationships, and it's usually right. But it can get fooled. If your fact table hasn't been loaded with data yet, or if you're working with a subset where values happen to be unique, Power BI might incorrectly set a relationship as one-to-one. Always check.

One-to-One (Rarely Necessary)

Both columns contain unique values. This usually means you have two tables that should be one table. There are exceptions - maybe you've split a wide table for performance reasons, or you're joining reference data that genuinely has a one-to-one correspondence. But if you find yourself creating a one-to-one relationship, stop and ask whether you actually need two separate tables.

Many-to-Many (Use with Caution)

Both columns can contain duplicates. This is where things get tricky. Many-to-many relationships create what Power BI calls "limited relationships" - they use inner joins instead of outer joins, the RELATED function doesn't work across them, and they can produce unexpected results if you're not careful.

The legitimate use case is when you genuinely have a many-to-many business relationship. A classic example: employees can belong to multiple departments, and departments have multiple employees. You model this with a bridge table (a fact table recording each employee-department assignment) and two one-to-many relationships. That's cleaner and more predictable than a direct many-to-many.

We had a client try to use a many-to-many relationship between a Budget table (budgets at category level) and a Sales table (sales at product level). Products roll up to categories, but not through a direct relationship. The correct solution was a proper star schema with a Product dimension containing a Category column, allowing both facts to filter through the same dimension hierarchy.

Cross-Filter Direction - The Source of Much Confusion

By default, one-to-many relationships filter in one direction: from the "one" side to the "many" side. This is almost always what you want.

Bi-directional filtering means filters also flow from the "many" side to the "one" side. People enable this when they want a fact table to filter a dimension table - for example, showing only the products that have sales in a particular period.

Here's my honest opinion: bi-directional filtering is overused and misunderstood. In about 80% of cases where I see it enabled, there's a better solution using DAX (CROSSFILTER, TREATAS, or a measure with appropriate filter context). Bi-directional filtering has performance costs, can create ambiguous filter paths that Power BI can't resolve, and makes the model harder to reason about.

When is it genuinely useful? Row-level security scenarios sometimes require it. And there are specific visual requirements where you need dimension tables to show only values that exist in the filtered fact data. But even then, consider whether a DAX measure using CALCULATETABLE or TREATAS might be cleaner.

If your model has more than two bi-directional relationships, that's a smell. Something in your model design probably needs rethinking.

Active vs Inactive Relationships

Power BI only allows one active relationship path between any two tables. But you can create additional inactive relationships. This comes up most often with role-playing dimensions - a Date table that relates to both OrderDate and ShipDate on your Sales table.

You can only have one of those relationships active. The other one is inactive (shown as a dashed line in the model diagram). To use the inactive relationship in a measure, you use the USERELATIONSHIP function:

Ship Date Sales = CALCULATE(SUM(Sales[Amount]), USERELATIONSHIP(Sales[ShipDate], 'Date'[Date]))

The alternative is to create separate date tables for each role - one for Order Date, one for Ship Date. This uses more memory but means all relationships can be active, and report authors don't need to worry about which measures use which relationship. For models that will be maintained by people who aren't DAX experts, separate tables are often the pragmatic choice.

The DateTime Gotcha

This one catches people regularly. You create a relationship between a Date table and a fact table, both columns are set to "Date" type in the modelling tab, but the relationship doesn't filter correctly. Some dates match, others don't.

The issue is that Power BI's storage engine uses DateTime internally. Even if you've formatted a column as Date, it might still carry a time component underneath. Your Date table has January 15, 2025 at midnight (00:00:00), but your fact table has January 15, 2025 at 3:47 PM. They don't match.

The fix: strip the time component in Power Query before the data hits your model. Use Date.From([YourDateTimeColumn]) in Power Query to truncate to date only. Do this on both sides of the relationship. It takes thirty seconds and prevents hours of debugging.

Referential Integrity - Free Performance If Your Data Supports It

For DirectQuery models, there's a relationship property called "Assume referential integrity." When enabled, Power BI generates inner joins instead of outer joins in the SQL it sends to your source database. Inner joins are typically faster.

The catch is in the name: "assume." If your data doesn't actually have referential integrity - if there are sales records pointing to product IDs that don't exist in the Product table - those orphaned rows will silently disappear from your results. Not an error. Just missing data.

If your source database enforces foreign key constraints, enable this setting. If it doesn't, test carefully before enabling it. Check for orphan records first. We've seen cases where enabling this property caused a 40% improvement in query performance, so it's worth investigating.

The Pattern We Recommend

For most Australian organisations building Power BI models, here's what works:

  1. Design a star schema. Dimension tables around the outside, fact tables in the middle.
  2. Use one-to-many relationships everywhere possible.
  3. Keep cross-filter direction as single (not bi-directional) unless you have a specific, documented reason.
  4. Create separate date tables for role-playing dimensions if your team isn't comfortable with USERELATIONSHIP.
  5. Strip time components from date columns in Power Query.
  6. Check cardinality auto-detection. Don't just trust it.
  7. Keep relationships active. Use inactive relationships only when you genuinely need role-playing dimensions.

If you're working with a model that doesn't follow these patterns and you're seeing unexpected results, the relationship design is the first place to look.

Getting Expert Help

Data modelling mistakes compound over time. A bad relationship design doesn't just affect one report - it affects every measure, every visual, and every decision made using those numbers. If your organisation is building out Power BI and wants the foundations done properly, our Power BI consultants work with Australian businesses on exactly this kind of structural work.

For organisations looking at broader data strategy - connecting Power BI to proper data warehouses, implementing Microsoft Fabric, or building out a complete analytics platform - our business intelligence practice covers the full picture. And if you're curious about how Microsoft Fabric fits into your analytics stack, we can help you figure out whether it makes sense for your specific situation.

Relationships are the skeleton of your Power BI model. Everything else hangs off them. Get them right and your model will be fast, predictable, and trustworthy. Get them wrong and you'll spend more time explaining why numbers don't match than actually using them.