Data Modeling and Relationships

Picture the files behind a small online shop. One spreadsheet lists every sale: a date, a product code, a customer number, and a dollar amount. A second file describes the products: name, category, cost. A third holds customers: name, city, signup date. A fourth is a calendar. Each file is tidy on its own. Then your boss asks a simple question: “What did we sell to customers in Texas last quarter, broken down by product category?” The answer lives in all four files at once, and no single one can produce it alone.

This is the moment Power BI is built for. But before you can drag a chart onto the page, Power BI needs to know how those four tables connect. Teaching it those connections is called data modeling, and it is the quiet work that makes every report afterward feel easy.

Why this matters

Most people who open Power BI want to make a chart. They import their data, click a bar chart, and get a wrong number or a flat gray “can’t display” message. Almost always the cause is the same: the tables are not connected, or they are connected the wrong way.

A data model is the set of connections between your tables. Get it right once, and every chart, filter, and total you build later just works. Get it wrong, and you will fight the same confusing errors on every single visual. The model is the foundation. A crooked foundation means a crooked house, no matter how nice the paint.

The good news: the core ideas are simpler than they sound, and Power BI does much of the setup for you. Your job is to understand what it is doing so you can fix it when it guesses wrong.

What a data model is (and why it beats one giant table)

A data model is a group of tables joined by relationships. A relationship is a link between two tables that share a common piece of information, such as a product code that appears in both your sales table and your product table. The link tells Power BI, “the code A100 over here is the same product as A100 over there.”

New users often ask why they can’t just paste everything into one enormous spreadsheet: every sale, plus the product name and category, plus the customer’s city, all on the same row. That single wide sheet is called a flat table, and it seems simpler at first. It causes three real problems.

First, it repeats itself endlessly. If you sold 10,000 items, the word “Electronics” gets typed into 10,000 rows. A separate product table stores “Electronics” once. Repetition wastes space and, worse, invites mistakes.

Second, updates become dangerous. Rename a category in a flat table and you must fix thousands of rows correctly or your report splits one category into two. In a model, you change it in one place.

Third, and most important for reporting, a flat table makes it hard to slice data cleanly. Power BI is designed to filter and group using small description tables and to add up numbers from one big event table. That split is exactly what a real model gives you.

Model view: where connections live

Power BI Desktop has a few main views along the left edge of the window. One shows your report canvas, one shows raw table data, and one shows the model itself. Select the Model view icon on the left side of the window to open it.

Model view shows every table as a box listing its columns, with lines drawn between tables that are related. Hover over a line and Power BI highlights the two columns that connect. This diagram is your map. When a report misbehaves, this is the first place to look.

When you load two or more tables at once, Power BI tries to detect relationships for you by matching column names. It is often right. But it only creates a link when it is confident, so you will regularly need to add or correct one yourself.

Try It Open a file that has at least two related tables, such as a Sales table and a Products table that both contain a product code. Do this:

Select the Model view icon on the left side of the window.

Find the two tables. Click the product code column in your Sales table and hold the mouse button down.

Drag it on top of the matching product code column in your Products table, then let go.

A line appears between the tables. That line is your relationship. To confirm it, hover over the line and check that it highlights the two code columns.

Prefer menus? In Model view, open the Home ribbon and select Manage relationships, then New. Pick the first table and its code column, pick the second table and its code column, then select OK.

What did you notice about which way the small arrow on the line points?

Cardinality and filter direction, in plain words

Once a relationship exists, Power BI describes it with a word that sounds technical but is not: cardinality. Cardinality just answers “how many rows on each side match up?”

The common case is one-to-many. One row on one side matches many rows on the other. You have one row for the product A100 in your Products table, and that product shows up in many rows of your Sales table because you sold it many times. Power BI writes this as 1 on the product side and * (a star, meaning “many”) on the sales side, shown right on the relationship line.

For this to work, the “one” side must hold a unique list. Each product code can appear only once in the Products table. If a code shows up twice there, Power BI cannot tell which one a sale refers to, and it will refuse to build the relationship until you fix it. This rule matters, so remember it: the “one” side must have no duplicates.

Now the part that unlocks everything. A relationship also has a direction, and filters flow along it. By default, filters travel from the “one” side to the “many” side.

Here is what that means in practice. When you pick “Electronics” from your Products table, the filter flows down to Sales and keeps only the sales of electronics. Power BI then adds up those sales. Choosing a customer city or a month works the same way: the small description table filters the big sales table, and your total updates. The little arrow in the middle of the relationship line shows this flow. Understanding this one idea explains most of why a chart shows what it shows.

You can set a relationship to filter in both directions, but that is an advanced move that can produce confusing results. For nearly everything you build early on, single direction from the “one” side is what you want.

The star schema

Line those ideas up and a shape appears. Put your big event table in the center. Surround it with the description tables that filter it. The result looks like a star, so it has a name: a star schema. It is the standard, recommended way to build a Power BI model.

Two kinds of tables make up a star.

A fact table sits in the middle and records events: one row per thing that happened. Sales, shipments, support tickets, temperature readings. Fact tables hold the numbers you add up, plus code columns that point out to the description tables. They tend to be large and to grow every day.

A dimension table describes the things involved in those events: products, customers, dates, regions. Dimension tables hold the words you filter and group by, and they sit on the “one” side of each relationship. They are usually small and change slowly.

Here is the shop from the opening, drawn as a star. Sales is the fact table. The four description tables around it are dimensions. The 1 and * marks show the one-to-many direction, and filters flow inward from each dimension to Sales.

                 +-----------------+
                 |     Product     |
                 |  (what sold)    |
                 +-----------------+
                          | 1
                          v *
 +--------------+  1   +-----------------+   *  +--------------+
 |   Customer   |----->|      Sales      |<-----|     Date     |
 | (who bought) |      |  (each sale:    |    1 |    (when)     |
 +--------------+   *  |  amount, units) |      +--------------+
                       +-----------------+
                          ^ *
                          | 1
                 +-----------------+
                 |     Region      |
                 |    (where)      |
                 +-----------------+

Read it like this: one product, many sales. One customer, many sales. One date, many sales. One region, many sales. Every arrow points into the center, and the center is where the money is counted. When your model looks like this, Power BI can answer “electronics sold to Texas customers last quarter” by letting three dimensions filter the fact table at the same time.

The Date table earns its own spot

Notice that the calendar got its own box in the star. That is deliberate. Time questions are the most common questions in business reporting: sales this month versus last, year-to-date totals, growth over the same quarter a year ago. Power BI answers these well, but only when dates live in a dedicated Date table rather than being buried as a column inside your sales data.

A good Date table has one row for every single day across the full span of your data, with no gaps and no repeats. You then connect it to your fact table’s date column, one-to-many, just like any other dimension.

To tell Power BI this table is your official calendar, select the table in the Data pane, then use Mark as date table (available by right-clicking the table or from the Table tools ribbon). This unlocks the time calculations you will meet in the next chapter.

You do not need to write those calculations yet. Just build the Date table and connect it now, so the foundation is ready. Chapter 6 on DAX picks up exactly here.

In business

A regional sales manager keeps three exports from the company system: an orders file, a store-locations file, and a sales-reps file. Every Monday she used to spend an hour pasting them into one giant sheet and building the same pivot from scratch.

Now she loads all three into Power BI once. She makes the orders file her fact table. Store locations and sales reps become dimension tables, each joined to orders by a shared ID, one-to-many. She adds a Date table and marks it. The model is a small, clean star.

From then on, every Monday is a refresh button. She clicks a store on a map and sales for that store appear. She clicks a rep and sees their orders by month. She never rebuilds anything, because the relationships carry the filters for her. The hour of pasting became a minute of clicking, and the numbers stopped disagreeing between reports.

Common pitfalls

The one giant flat table. Loading everything as a single wide sheet feels fast and quietly limits you later. You lose the clean filtering that dimension tables provide, updates get risky, and the file bloats. Split events from descriptions into a star instead.

Duplicates on the “one” side. If the table meant to be the “one” side has a repeated key, Power BI will not let you make the relationship one-to-many, or your totals will double up. Make sure each product, customer, or date appears exactly once in its dimension table.

No dedicated Date table. Relying on a stray date column inside your sales data blocks the time calculations you will want most. Add a real Date table and mark it early.

Turning on both-direction filtering to “fix” a chart. When a visual looks wrong, switching a relationship to filter in both directions can seem like a quick patch. It often creates new, harder-to-spot errors. Leave relationships filtering in a single direction from the “one” side unless you have a clear reason and know the trade-off.

Trusting auto-detect blindly. Power BI’s automatic relationships are usually right and occasionally wrong. Open Model view after loading data and read the lines. Two minutes of checking saves an afternoon of chasing bad numbers.

Practice

Open Power BI Desktop with a fact table and at least two dimension tables. Switch to Model view and describe out loud which table is your fact table and why.
Create one relationship by dragging a shared key field from the fact table onto the matching field in a dimension table. Hover over the new line and confirm it links the right columns.
Find the 1 and * marks on that line. Say which side is “one,” which is “many,” and which way the filter arrow points.
Sketch your own tables as a star on paper: fact in the center, dimensions around it. If any table does not fit the star, ask whether it is truly a description table or whether it belongs somewhere else.
Add a Date table if you do not have one, connect it to your fact table’s date column, and mark it as a date table. You are now set up for the visuals in Chapter 5 and the DAX calculations in Chapter 6.

Part of the Power BI track. Developed with AI assistance and reviewed by a human editor. Power BI changes often; menu names, features, and prices may have shifted since the “checked on” dates in this chapter. Verify anything critical against Microsoft’s official documentation.