Power BI Relationships - Simply Explained

Welcome to the M365 FM podcast, where we explore the people, ideas and technologies shaping the future of work. Why your numbers go wrong when tables don't talk. You can build a clean power BI report that hands you the wrong numbers. You have five tables, clean columns, a report that looks finished, and every row still shows the same total. The data is fine and the tables simply aren't talking to each other. A relationship is the wire between two tables built on one column they share. Learn to read that wire, and you can predict exactly what a click does to your numbers, what a relationship is in plain terms. There's a scene that plays out for almost everyone who opens power BI for the first time. You pull five tables in, you drag a few fields onto a chart, and the chart shows the same number in every row. Your first instinct is to blame the data, so you open the source file and the data is correct. The problem sits somewhere else, and it's usually the most boring object in the whole model. A relationship is a filter pathway between two tables. That's the entire definition. It runs along one column that both tables share, and it lets a filter you apply on one table reach the other. Nothing else is happening under the hood, which is why relationships feel far less mysterious once you can picture them. Take a simple model with four tables category product sales and year. Category connects to product. Product connects to sales and year. Connects to sales to. Now you click a single category, say cat a that click travels from category into product and product tells power BI which two products belong to that category. From there, the filter travels into sales and sales narrows to just the rows for those two products. If you also filter for one year, sales narrows again. Microsoft's own walkthrough lands on eleven units when you filter one category and one year together, and fourteen units when you filter the category on its own. The difference comes from a small rule worth holding on to when two filters land on the same table. Both of them have to be true. Power BI combines filters with an and. Every single time. Unless you write a formula that says otherwise. That's why one extra filter can shrink a number far more than you expect, and why an empty chart sometimes just means two filters disagree. There's one case where a table has no relationship on purpose. A disconnected table doesn't filter anything at all, it just holds a value you want to use. Like a currency rate. You pick from a dropdown and a measure reads that value and does something useful with it. Power BI is what if parameter builds one of these for you. So a relationship isn't a formula and it isn't decoration you add at the end. It's a pathway, and every number in your report is only as trustworthy as the paths that lead into it. Cardinality. The word that decides which table leads cardinality sounds like one of those words designed to scare beginners. Strip it down, and it's really asking how many times a value can repeat on each side of a link picture, a product table, and a sales table. The product table holds one row per product, so each product ID shows up exactly once the sales table holds thousands of rows and the same product ID appears again and again, once for every sale of that product. That's all cardinality is. One side holds the unique list and the other side holds the repeats. Power BI gives you four ways to describe that link one to many, many to one, one to one, and many to many. The first two are the same link seen from opposite ends, so you don't need to memorize them as separate things. If the unique list sits on the product side, the link is one to many when you read it from products and many to one when you read it from sales, it's the same link seen from either end. One to one means both columns hold unique values, and it's rare enough that you can treat it as a warning sign. It usually means you split something that would have been happier as a single table. Many to many means both columns can repeat. It's real. It solves a few hard problems, and it charges you for the privilege. So reach for it on purpose rather than by default. The workhorse is one to many and you will build almost everything on top of it. One clean list of things on one side and all the transactions on the other. Once you can spot which table holds the unique list, you found, the side that leads, and the side that follows. Power BI usually sorts this out for you. When data loads, it studies the columns and guesses the cardinality, and most of the time the guess is right. Two things are worth knowing, though it guesses badly when the tables haven't loaded any data yet, and it guesses badly when a column that should hold duplicates happens to hold unique values today. There's also a hard rule underneath all of it. If a refresh ever tries to load a duplicate into the unique side. The entire refresh fails. Which way the filters travel? Even a correct one. To many, link has a direction and this is the setting most beginners never open. It's called cross filter direction and it decides which table is allowed to filter which. The default is single and it's the safe choice. Filters flow from the unique side to the repeating side, and that's where they stop. Your product list can filter your sales, but your sales can't filter your product list for most reports, that's exactly the behavior you want because it's predictable and it stays fast. The other option is both, which people call bidirectional. You reach for it when a filter needs to travel back up the link, usually because a slicer is behaving strangely. Microsoft's bank example makes the reason plain customers and accounts are linked through a middle table, because one customer can hold several accounts, and one account can have several owners with the filter pointing only one way, every customer ends up showing the same balance, which is impossible in real life. Flip one link to both directions and the customer balances suddenly make sense? Both directions is not free, though. Bidirectional links cost performance, and they can create more than one valid path for a filter to travel between two tables. When two paths look equally good, power BI can't choose, and it hands you an ambiguous path error. Microsoft's guidance is blunt about this. Keep bidirectional relationships to a minimum and switch one on only when you actually need it, so the habit worth building is simple leave the direction alone until a filter genuinely can't reach where it needs to go, and only then open the setting and change it. If you ever can't remember which way a filter should flow, picture the unique list as the boss. It sends instructions down to the transactions, and the transactions rarely need to send anything back. Reading the solid and dashed lines. Open the model view in power BI and you'll see tables joined by lines. Those lines carry useful information, and reading them takes about a minute to learn. A solid line means the relationship is active, and a dashed line means it's inactive. One rule makes the dashed lines make sense. Between any two tables, only one path can be active at a time, and any others have to sit inactive until a formula switches them on. You might wonder why you'd ever want more than one path. A single table can mean several different things. Picture one date table which could filter your sales by order, date by ship date or by delivery date. That's three jobs for one table, and power BI will only let one of them be live by default. Microsoft calls this a role playing dimension, and the usual fix is refreshingly simple. Copy the date table into a ship date table and a delivery date table, then give each copy its own active line date. Tables are small, so the duplicate costs almost nothing. There's a second route that avoids the copying. A formula called user relationship can wake up an inactive line for one calculation without you duplicating a table. It's worth knowing the name so the tutorial stops sounding foreign, but you don't need it on your first day. The real point of all this is that the diagram is a map and maps can be read. Solid means live. Dashed means sleeping. One arrowhead means a filter travels in one direction, and two arrowheads mean it travels both ways. Once those marks make sense. A model stops being a picture and starts being something you can reason about. The star shape that keeps a model simple. All of this gets easier if your tables are shaped the right way before you start linking them. The shape Microsoft recommends is the star, and the name tells you what it looks like. At the center sits one big table of events. Around it sits several smaller tables that describe things. The small ones are dimensions, and they're the things you describe products, customers, dates, regions. The big one is a fact table and it holds what happened. Sales orders, balances, dimensions are there for filtering and grouping, and facts are there for adding up. This is the part that surprises people. Nothing in power BI has a checkbox that says this is a fact table. The relationship itself decides the role. The unique side of a link always becomes the dimension, and the repeating side always becomes the fact. So the shape falls out of the links you build, not from a setting you choose. The star wins for two practical reasons. Filters travel short distances, which keeps reports quick, and you can only build a clean drill down hierarchy inside a single table, which is exactly what dimension tables are. A common alternative is the snowflake, where you split one entity across several tables like category into subcategory into product. It looks tidy on paper, but it means more tables to manage longer filter chains and no need to drill down at the end of it. A date table is the one you'll find in almost every store, because nearly every business question is some version of how did this change over time? One small trick is worth filing away. If a table has no single column of unique values, you can add one. For example, an index column. Then copy that column into the other table so both sides line up. That added column is called a surrogate key, and it's the standard way to give a dimension table something to lead with. Get the shape right and the links almost build themselves. Get it wrong and you'll find the same errors on every refresh. The rules a relationship has to follow. A relationship looks for giving until you try to build one by hand and then you meet the rules. There are only a few, and together they explain almost every error message you'll run into. First, a relationship runs between one column and one column. You can't relate a column to another column inside the same table, no matter how much sense it would make. Parent and child rows that live in one table need a formula, not a relationship. Second, the two columns should share a data type. You're allowed to link a text column to a number column, and power BI won't stop you, but no values will ever match. So nothing filters and you're left staring at a blank chart. Third, the unique side has to genuinely be unique. This is the rule behind the error beginners meet first. The one that says one of the columns must have unique values. When it appears you have two clean options, you can remove duplicates, though you should be careful because duplicated keys are often there for a good reason. Or you can add a small table holding just the distinct keys and link it to both sides. Then there's the date and time trap and it catches nearly everyone. Once power by stores date and time together in a single value, and the formatting you see on screen is only a display choice. So a date that looks identical to another date can fail to match because the hidden time part is different. The fix is to strip the time out in Power Query before the data ever reaches the model last. And this is the one behind the strangest looking reports. A relationship doesn't check your data for you. If a sale points at a product that doesn't exist in the product table, power BI won't refuse it. It places that sale in a blank group so the number isn't lost. When you see an empty row in a slicer and the source has no blanks at all, that's what you're looking at. The real fix belongs in the source data, not in the model. One comfort is worth ending on. Power BI usually builds these relationships for you. When data loads, it reads the column names and when it can't find a confident match, it stays quiet instead of guessing wildly. Where beginners get burned, you can learn every rule and still lose an afternoon, so it helps to know the symptoms and what they usually mean. The most common one by far is the same number sitting in every row. Microsoft lists this as the top sign of a missing relationship. It means the tables aren't connected or the connection exists, but isn't passing filters along. Check that the tables are related at all, then check that the link is active and pointing the right way. Microsoft's own advice is to switch the visual to a plain table first, because seeing the raw rows makes a relationship problem obvious in a way a chart never does. Next is a chart that shows nothing before you touch a relationship. Make sure the table is loaded with rows and that something is sitting in your filters. A surprising number of blank charts come from an empty table or a filter that excludes everything, not from a broken link. Then there's the report that looks right but is wrong underneath. Most of the time. The wrong columns are related, like a product ID wired to a customer ID, the chart still renders, and the numbers still add up to something, which is exactly why this one is dangerous. It's also why you should always check which two columns align connects instead of trusting that a line exists at all, you'll also meet the empty group in the slicer. That's the blank row from the last section, a fact pointing at something missing on the unique side. It's a data quality signal wearing a costume. Two more traps are worth naming, joining two fact tables straight to each other with a many. To many link does work, but it leaves you able to filter by only one of the tables and data problems can silently drop rows and reaching for bidirectional links to force a slicer to behave is how people end up with an ambiguous path error, and no idea why. The habit that prevents most of this is unglamorous. Build the star. Keep one active, one to many. Link between each pair of tables and read the diagram before you blame the data. What a relationship can't do. It's worth being honest about the ceiling, because a relationship is a narrow tool that does one job very well. It doesn't clean your data. A relationship points at a column, and if that column is messy, the relationship spreads the mess everywhere the filter reaches. It doesn't enforce integrity either, so it will never refuse a bad row. It just parks that row in a blank group and carries on. It can't connect two columns inside the same table, and it can't have more than one live path between the same two tables at once. Both of those limits are why the duplicate date table trick and parent child formulas exist in the first place. It also can't fix a mismatch in detail if your sales are recorded per product, but your targets are set per category. A plain one to many. Link won't tell an honest story because the two tables aren't at the same level. Solving that takes measures and a bit of care, not a cleverer link. Many to many. Cardinality is real and it does solve some of these problems. It comes with a cost, though. Those links can't use the shortcut that pulls a value straight from the unique side, and they run slower than a clean one. To many, they're a tool, not a default. One thing a relationship will never do is tell you that a number is wrong. It has no opinion about whether a result makes sense, that judgment stays with you. And it's the part of this work that no setting can take over The plainest way to hold all of this is one sentence. Relationships move filters. They don't fix facts where this fits in Microsoft three hundred sixty five and fabric Power BI is where you build the model and the report and relationships work the same way, whether you're in the desktop app or in the browser, the idea doesn't change as the tools around it grow. One naming change is worth knowing because it trips people up in tutorials. The object that holds your tables and relationships used to be called a data set. Microsoft renamed it to a semantic model, and the new name is spreading everywhere. So if you see semantic model in a recent video, it's the same object you already know in Microsoft Fabric. That semantic model is a full item in its own right, sitting next to warehouses, lake houses, and mirrored databases. Fabric is Microsoft's single data platform, and one Lake is the one store all of that data lives in. You don't need the whole platform to use relationships, but it helps to see where your report sits in the bigger picture. There are three ways power BI can hold your data, and they trade freshness against speed. Import copies the data into memory, which makes reports fast, but only as current as the last refresh. Direct query reads the source live, which is always current but usually slower. Direct lake loads from one lake and gets close to import speed with a refresh that copies only metadata and can finish in seconds. Direct Lake needs fabric capacity, and Microsoft is retiring the older power BI premium capacity in favor of it. So that's the direction things are heading. Import and direct query work with any power BI license, including the free one, so you can learn everything in this episode without paying for capacity. So here's the short version. To take away a relationship is a filter pathway built on one shared column. The unique side leads and the many side follows the direction and the active or inactive state. Decide how far a filter travels and the star shape keeps the whole thing simple. The tool moves filters and it doesn't fix facts. I'll leave you with one question in your own report. Which table has a column full of repeats that nobody has ever connected to anything? If this made the topic feel less intimidating, subscribe to M365 FM to master the tools and the mindset shaping the future of work.