Relationships are the foundation of every Power BI report — get them wrong and your data will lie to you. This lesson teaches you exactly how to connect tables, choose the right cardinality, control filter direction, and fix the mistakes that trip up most beginners.
Imagine you've pulled your company's sales data into Power BI. You have one table full of individual transactions — every order, every line item, every customer ID. You have another table with customer details: names, cities, account tiers. You want to build a report that shows total revenue by city. Simple enough, right?
Except when you drag "City" from your customers table onto a chart and add "Revenue" from your transactions table, nothing makes sense. The numbers are wrong, or everything shows the same value, or Power BI throws a warning you don't understand. This is the moment every new Power BI user hits — and it's the moment when understanding relationships goes from an abstract concept to an urgent practical need.
Relationships are the connective tissue of your data model. They tell Power BI how tables talk to each other — which column in Table A corresponds to which column in Table B — so that when you slice your data one way, everything else filters correctly. Get them right and your reports feel almost magical. Get them wrong and your numbers will lie to you confidently. By the end of this lesson, you'll know exactly how to create relationships in Power BI, what cardinality and cross-filter direction mean and why they matter, and how to spot and fix the modeling mistakes that trip up most beginners.
What you'll learn:
You don't need to be a data expert to follow this lesson, but you should have:
That's genuinely all you need. We'll build everything else from scratch.
Before touching Power BI, let's make the concept concrete with a real-world analogy.
Think about a library. The library has two records systems: a books catalog and a member checkout log. The books catalog has one row per book — title, author, genre, shelf location. The checkout log has one row per checkout event — which member checked out a book, on what date, when they returned it.
These two systems are connected by a shared piece of information: the book's ID number. Every row in the checkout log references a book ID that appears in the books catalog. That shared ID is the bridge between the two tables.
In Power BI, that bridge is called a relationship. A relationship tells Power BI: "The BookID column in the Checkouts table points to the BookID column in the BooksCatalog table." Once Power BI knows that, it can answer questions like "How many times was each genre checked out?" by following that bridge between the two tables.
The column that acts as the bridge is called a key. The table that contains one unique entry per key (the books catalog, where each book ID appears exactly once) is called the lookup table or dimension table. The table that contains many entries referencing that key (the checkout log, where the same book ID might appear dozens of times) is called the fact table.
This distinction — one unique side versus many repeated side — is the heart of what we call cardinality, and we'll come back to it in detail shortly.
Throughout this lesson, we'll work with a realistic but simple retail scenario. Imagine we have three tables:
Sales (the fact table — many rows, one per transaction):
OrderID | CustomerID | ProductID | SaleDate | Quantity | Revenue
1001 | C001 | P010 | 2024-01-03 | 2 | 149.98
1002 | C003 | P022 | 2024-01-03 | 1 | 89.99
1003 | C001 | P010 | 2024-01-05 | 3 | 224.97
Customers (a dimension table — one row per customer):
CustomerID | FirstName | LastName | City | AccountTier
C001 | Maria | Santos | Chicago | Gold
C002 | James | Okafor | Houston | Silver
C003 | Lin | Wei | Chicago | Platinum
Products (another dimension table — one row per product):
ProductID | ProductName | Category | UnitPrice
P010 | Trail Runner X2 | Footwear | 74.99
P022 | Daypack 30L | Accessories | 89.99
P031 | Fleece Vest | Apparel | 64.99
Notice the pattern: Sales references both CustomerID and ProductID. Those same IDs appear in the Customers and Products tables respectively. This is the classic star schema — a central fact table surrounded by dimension tables — and it's the foundation of good Power BI data modeling.
In Power BI Desktop, there are three main views accessible from the left-side panel of icons:
Click the Model view icon (the one that looks like three rectangles connected by lines). This is where all relationship work happens. You'll see your tables represented as cards, each showing their column names. If Power BI detected any relationships automatically when you loaded the data, you'll see lines connecting the cards already.
If the tables are piled on top of each other, click and drag them apart to arrange them in a way that makes sense — the star schema layout works well, with Sales in the center and Customers and Products off to the sides.
Power BI gives you two ways to create a relationship: drag-and-drop (fast and intuitive) and the Edit Relationship dialog (precise and complete). Let's use both.
In the Model view, find the Sales table card and locate the CustomerID column. Click and hold on CustomerID, then drag it over to the Customers table and drop it onto the CustomerID column there. Release the mouse.
A line will appear connecting the two tables. That line is your relationship. You'll notice it has a label at each end — likely "1" on the Customers side and "*" (asterisk) on the Sales side. Those symbols are Power BI's notation for cardinality, and they're telling you something important: each customer appears once in Customers, but can appear many times in Sales. We'll unpack this fully in the next section.
Now repeat the process: drag ProductID from Sales and drop it onto ProductID in Products. You should now have two relationship lines in your model.
Go to the top menu ribbon, click the Modeling tab, and then click Manage Relationships. A dialog box opens showing all your current relationships.
Click New to create a relationship manually. A dialog appears with two dropdowns — one for each table in the relationship. Select Sales in the first dropdown and click the CustomerID column to highlight it. In the second dropdown, select Customers and click CustomerID. Power BI will show you a preview of the matched rows at the bottom of the dialog, which is useful for verifying you've chosen the right columns.
Below those table previews, you'll see options for Cardinality and Cross filter direction — two settings that are critical to get right. Leave them at their defaults for now and click OK. We'll come back to edit them.
Tip: The Manage Relationships dialog is also where you go to edit or delete existing relationships. Double-click any relationship line in the Model view to open the Edit Relationship dialog for that specific connection.
Cardinality describes the numerical relationship between the rows on each side of a connection. Power BI offers three types:
This is the most common type and the one you should default to whenever possible. In our example, the relationship between Sales and Customers is many-to-one: many rows in Sales can reference the same CustomerID, but each CustomerID appears only once in Customers.
Power BI represents this as * on the "many" side and 1 on the "one" side. The 1 side is always your dimension/lookup table, and the * side is always your fact table.
This setup is powerful because it's unambiguous. When a filter is applied — say, you click on "Chicago" in a visual — Power BI knows exactly which customers live in Chicago, looks up their IDs, and then finds all the matching transactions in Sales. Clean, predictable, correct.
Both tables have only one matching row for each key value. This is rare in practice and usually a sign that the two tables could simply be merged into one. An example might be a Customers table and a CustomerPreferences table where each customer has exactly one set of preferences. Power BI supports this, but if you find yourself creating many one-to-one relationships, reconsider your data structure.
Both sides can have repeated values of the key. This is the relationship type that causes the most confusion and the most problems.
Imagine you had a table of Promotions where each promotion could apply to multiple products, and each product could be part of multiple promotions. Neither side has unique keys. Power BI can handle this with a many-to-many cardinality setting, but it requires careful thought about what filters should do.
Warning: Many-to-many relationships in Power BI can produce unexpected results if you don't understand them deeply. As a beginner, if you find yourself needing one, it's often a signal that your data model needs restructuring — perhaps with a bridge table in between. Stick to one-to-many relationships until you're comfortable with the fundamentals.
How Power BI detects cardinality automatically: When you drag to create a relationship, Power BI scans both columns. If one column has all unique values and the other has repeats, it sets the cardinality to many-to-one automatically. If both columns have repeats, it'll warn you or set many-to-many. Always glance at the cardinality symbols after creating a relationship to confirm Power BI got it right.
Once two tables are connected, you need to tell Power BI: when a filter is applied to one table, does it automatically flow to the other table?
This is called cross-filter direction, and it has two options: Single and Both.
With single direction, filters flow from the 1 side (dimension table) to the * side (fact table). This is the default and it matches how most analysis works.
In our model: if you select "Gold" from the Customers AccountTier column, Power BI filters the Customers table to only Gold customers, then uses the relationship to filter Sales to show only transactions from those Gold customers. The filter travels from Customers → Sales. That's exactly what you want.
What doesn't happen with single direction: selecting a value in Sales (the * side) does not automatically filter Customers (the 1 side). In most scenarios, this is the correct behavior — you don't want clicking on one transaction to filter your customer list.
With bidirectional filtering, filters flow both ways. A selection in Sales can filter Customers, and a selection in Customers can filter Sales.
This sounds convenient, and sometimes it is — particularly in complex models with multiple fact tables. But bidirectional filtering introduces serious risks:
Tip: Start with Single direction for all relationships. Only switch to Both when you have a specific, identified reason — and test your report thoroughly when you do.
To check or change the cross-filter direction of any relationship, double-click the relationship line in Model view. The Edit Relationship dialog will show you the current settings. The direction is also visualized on the relationship line itself: a single arrow pointing from the 1 side to the * side means Single direction; a double-headed arrow means Both.
Power BI only allows one active relationship between any two tables at a time. The active relationship is the one that automatically applies whenever you use fields from both tables in a visual. Active relationships are shown as solid lines in the Model view.
But sometimes you need more than one relationship between two tables. A common real-world example: your Sales table has two date columns — OrderDate and ShipDate. Your Calendar table (a standard dimension table with one row per date) can only have one active relationship to Sales at a time.
The solution is to create both relationships, letting one be active (solid line) and marking the other as inactive (shown as a dashed line in the Model view). You can then use the DAX function USERELATIONSHIP() in specific measures to activate the inactive relationship when needed.
This is an advanced topic, but it's worth knowing the concept exists so you're not confused when you see a dashed line in your model diagram.
Let's put everything together. Follow these steps using Power BI Desktop. If you have your own retail or transactional data, use it. Otherwise, you can create simple CSV files using the data from the "Your Practice Data Model" section above and load them via Home → Get Data → Text/CSV.
Step 1: Load your three tables.
Load Sales, Customers, and Products into Power BI. Navigate to Home → Get Data → Text/CSV for each file, or copy the sample data into Excel and load it via Home → Get Data → Excel Workbook.
Step 2: Open Model view.
Click the Model view icon on the left panel. Arrange the three table cards so Sales is in the center, Customers is to the left, and Products is to the right.
Step 3: Create the first relationship.
Drag CustomerID from Sales to CustomerID in Customers. Confirm that the line shows * on the Sales side and 1 on the Customers side. If it shows differently, double-click the line and correct the cardinality.
Step 4: Create the second relationship.
Drag ProductID from Sales to ProductID in Products. Same check — * on Sales, 1 on Products.
Step 5: Verify in the Manage Relationships dialog. Click Modeling → Manage Relationships. You should see two active relationships listed. Confirm both show "Many to One" cardinality and "Single" cross-filter direction.
Step 6: Test your model in Report view.
Switch to Report view. Insert a table visual. Add City from Customers, Category from Products, and Revenue from Sales. If your relationships are correct, you'll see revenue broken down by customer city and product category — data from three separate tables, combined correctly through your relationships. This is the payoff.
If CustomerID in your Customers table has duplicate values (two rows for customer C001), Power BI can't create a valid many-to-one relationship — because the "one" side isn't actually one. Power BI may set the cardinality to many-to-many instead, or throw a warning.
Fix: Clean your data. The dimension table's key column must be unique. Use Table view to check: click the CustomerID column header in your Customers table, then look at the column statistics panel (if enabled) or simply sort the column and visually scan for duplicates.
You drag CustomerID from Sales (stored as text, like "C001") to CustomerID in Customers (stored as a whole number, like 1001). Power BI can't match these correctly.
Fix: Before creating relationships, ensure the key columns on both sides have the same data type. Go to Power Query Editor (Home → Transform Data) and change the column type as needed. Whole Number, Text, and Decimal Number are the most common types — they must match.
You create three tables: Sales, Customers, and Regions. You connect Sales to Customers via CustomerID, and Customers to Regions via RegionID. Then you also try to connect Sales directly to Regions. Now you have a loop — Power BI can't determine which path to use when filtering.
Fix: Power BI will actually warn you about ambiguous relationships. Remove the redundant direct relationship. In a star schema, dimension tables should relate to the fact table only — not to each other.
This is the most common beginner mistake. You add City (from Customers) and Revenue (from Sales) to a chart, but there's no relationship between those tables. Power BI either throws an error or shows the same total revenue for every city.
Fix: Always check the Model view when visuals behave unexpectedly. If two tables have no line between them, they have no relationship, and cross-table analysis won't work.
You import a Products table where ProductID isn't unique because the export included product variants as separate rows with the same ID. Power BI creates a many-to-many relationship instead of many-to-one, and your revenue numbers become inflated or duplicated.
Fix: Deduplicate your dimension table in Power Query. Go to Home → Transform Data, right-click the ProductID column, and select Remove Duplicates. Then close and apply.
Here's what you've now learned to do:
The skills in this lesson are foundational to everything else in Power BI. Every DAX measure you write, every visual you build, every report you publish depends on a correctly structured data model underneath. If your relationships are wrong, your numbers are wrong — no matter how good the visual looks.
Where to go next:
CALCULATE(), RELATED(), and SUMX().Relationships are the moment Power BI stops being a chart tool and starts being a genuine analytics platform. You're now equipped to build models that actually work.