Beginner's guide to Power BI, part three: your first data model

A Power BI data model is your tables plus the relationships between them. To build your first one, load a sales table and its lookup tables, open Model view, check each relationship Power BI guessed, add a proper date table, then write one explicit measure. If the numbers change correctly when you slice, the model works.

This is part three of our beginner's series. Part one toured Power BI Desktop, and part two got data in from Excel. Part two promised more Power Query next; that now has a guide of its own, Power Query for beginners, so this part moves on to the step most beginners skip and later regret: joining the tables up. Every behaviour below is checked against Microsoft Learn as of October 2026.

What is a data model, and why does a beginner need one?

A data model is the set of tables you have loaded into Power BI, the relationships that connect them, and the measures that calculate over them. The relationships are what let a filter on one table, such as a customer slicer, change the numbers that come from another table, such as sales. Without them, each table is an island.

Microsoft's model relationships article puts the job of a relationship in one line: it "propagates filters applied on the column of one model table to a different model table". Everything else in this guide is about getting those filters to flow where you expect.

You can see what happens without one in Microsoft's own walkthrough of creating relationships. Two tables are loaded with no relationship between them, priority from one and hours from the other go into a table visual, and every row shows the same grand total. The visual has nothing telling it how the two tables connect, so it cannot split the number. If you ever see the same value repeated down a column, a missing or broken relationship is the first thing to check.

What will we build?

A deliberately small model, the kind most SME reporting starts from. Four tables:

Table	One row per	Key column	Role
Sales	Order line	CustomerID, ProductID, OrderDate	The numbers you add up
Customers	Customer	CustomerID (unique)	Lookup: name, region, sector
Products	Product	ProductID (unique)	Lookup: name, category, list price
Date	Calendar day	Date (unique)	Lookup: year, month, financial year

The pattern is one table of events surrounded by lookup tables that describe them. That shape has a name, the star schema, and our star schema guide explains the design reasoning and how to split a single flat export into these tables. This part is the hands-on version: assume you have the tables, and wire them up.

If your data arrives as one wide spreadsheet, do that split in Power Query first. You want each lookup table to have one row per thing, with a column whose values never repeat.

How do you load the tables and check what Power BI guessed?

Use Get data, tick all three source tables (Sales, Customers, Products) in the Navigator and load them together. Loading them in one go matters. Microsoft documents that when you query two or more tables at the same time, Power BI Desktop looks at column names and tries to create relationships for you. If it cannot determine a match "with a high level of confidence", it creates nothing.

Then select the Model view icon in the left-hand rail. You will see each table as a card, and any relationship Power BI created as a line between two cards. Learn to read the line before anything else:

• 1 and * at either end show the cardinality. The 1 sits on the lookup table, the * on the table with repeats.
• The arrowhead shows which way filters flow. One arrowhead means single direction; two mean both.
• A solid line is an active relationship. A dashed line is inactive and does nothing unless a measure switches it on.

In our example, autodetect should join Sales[CustomerID] to Customers[CustomerID] and Sales[ProductID] to Products[ProductID], because the names match exactly. Check both anyway. Autodetect is a guess based on names, and a column called ID in two unrelated tables is exactly the kind of name match that produces a wrong line.

How do you create a relationship yourself?

When autodetect misses one, the quickest way is to drag. In Model view, drag the key column from the lookup table onto the matching column in the Sales table and let go. Power BI opens the relationship editor with its best guess filled in.

The longer route, which shows you everything, is Manage relationships > New on the ribbon. Pick the first table and column, pick the second table and column, and Power BI fills in the options below. Check three of them before you select OK:

Setting	What to choose for a lookup table → Sales	Why
Cardinality	One to many (1:*) from the lookup table, or many to one (*:1) if you picked Sales first	They are the same relationship seen from either end
Cross filter direction	Single	Filters flow from the lookup table into Sales, which is what you add up
Make this relationship active	Ticked	Only one active path is allowed between two tables

If Power BI refuses with "One of the columns must have unique values", it means neither column qualifies as the "one" side. Usually your lookup table has a duplicate: the same customer ID twice, often with two spellings of the name. Fix it at source or in Power Query rather than by switching to many-to-many, which beginners should leave alone.

It is worth knowing early that a duplicate can arrive later, too. Microsoft notes that if a refresh tries to load duplicate values into a "one" side column, the entire refresh fails. A lookup table that is unique today because nobody has typed a customer in twice is not guaranteed to stay that way.

What should cross filter direction be?

Single, almost every time. Microsoft's guidance is to use bi-directional filtering "only as needed", because it can hurt query performance and can create ambiguous paths where Power BI has more than one route between two tables. In a star like ours, single-direction filters from each lookup table into Sales do everything a beginner's report needs.

The case that tempts people into Both is a slicer that should only show options with data, for example a product slicer that hides products nobody bought. Resist changing the relationship for that. Our star schema guide covers the measure-based alternative, and it keeps every other visual in the report behaving predictably.

How do you add a date table?

Power BI quietly creates a hidden date table behind every date column, a feature called auto date/time. Microsoft's auto date/time guidance lists the costs: each hidden table increases the model size and extends refresh time, the years always run January to December, and a date filter on one table cannot flow to another. That last point is what breaks the moment you add a budget table next to sales.

A single date table fixes all three. On the Modeling ribbon select New table and enter:

Date =
ADDCOLUMNS (
    CALENDAR ( DATE ( 2024, 4, 1 ), DATE ( 2027, 3, 31 ) ),
    "Year", YEAR ( [Date] ),
    "Month number", MONTH ( [Date] ),
    "Month", FORMAT ( [Date], "mmm yyyy" ),
    "FY start year", IF ( MONTH ( [Date] ) >= 4, YEAR ( [Date] ), YEAR ( [Date] ) - 1 )
)

Set the start and end dates to cover all your data. The last column gives a UK April-to-March financial year, which auto date/time cannot produce. Then relate Date[Date] to Sales[OrderDate], one to many, single direction, exactly like the other lookups.

Two details catch people here:

1. Strip the time. Microsoft's relationships article warns that the engine stores dates as date-and-time values, so an order date of 19 October at 14:32 will not match 19 October in your date table even if both columns are formatted as dates. Change the column type to Date in Power Query so the time portion is removed before it loads.
2. Mark it if you need to. Microsoft's date tables article says you must mark your date table if you use the classic time intelligence functions, or if you relate dates on a whole-number key such as 20261019. Right-click the table and choose Mark as date table. Power BI then checks that the date column has unique values, no blanks and no gaps, and removes the auto date/time table it would otherwise build. Microsoft's newer calendar-based time intelligence, still in preview as of October 2026, mostly does not need the mark.

Once you rely on your own date table, turn the auto date/time option off for new files under the Data Load options, so you do not carry the hidden tables into every future model.

How do you know the model works?

Write one explicit measure and test it. Select the Sales table and choose New measure:

Total Sales = SUM ( Sales[NetValue] )

Now build a table visual on a report page with Customers[Region], Products[Category] and Date[Month], and put Total Sales next to each in turn. You are looking for three things:

• The numbers change by row. If every row shows the grand total, the relationship to that lookup table is missing, inactive or pointing the wrong way.
• There is no (Blank) row. A blank row in a region or category list means Sales contains IDs that do not exist in the lookup table. Microsoft explains this as a "blank virtual row" that regular relationships add for unmatched values. It is telling you about a data problem, such as a product sold before it was added to the product list.
• The total matches your source. Compare one month against the figure in your finance system or spreadsheet. If it is off, check the date relationship and the time portion before anything else.

Why a measure rather than dragging the NetValue column straight onto the visual? It works either way in a simple report, but a named measure is reusable, can be formatted once, and is what every later calculation builds on. When you are ready for more, what is DAX? explains the language and DAX time intelligence covers year-to-date and year-on-year with the date table you have just built.

What should you tidy before building reports?

Three small jobs make the model far easier for whoever uses it, including you in six months:

• Hide the key columns. Right-click Sales[CustomerID], Sales[ProductID] and Sales[OrderDate] and choose Hide in report view. Report authors should slice by Customers[CustomerID] or Date[Month], from the lookup tables, never by the copies in Sales.
• Hide raw numeric columns once they have measures. If NetValue is covered by Total Sales, hide it so nobody sums a column that should not be summed, such as unit price.
• Name things for readers. Total Sales and Region beat Sum of net_val_gbp and REG_CD.

That is a working first model: four tables, three relationships, one measure, and a check that it answers correctly. It is also the base the rest of this series builds on. When models grow beyond this, our semantic models explainer covers what the model becomes once it is published, and our Power BI and Microsoft Fabric service shows how we build them for clients as part of our wider data and analytics work.

Frequently asked questions

What is a data model in Power BI?

A data model is the set of tables you have loaded into Power BI, the relationships that connect them, and the measures that calculate over them. The relationships are what let a filter on one table, such as a customer slicer, change the numbers that come from another table, such as sales. Without them, each table is an island.

Does Power BI create relationships automatically?

Often, yes. When you load two or more tables at once, Power BI Desktop looks at column names and creates a relationship where it is confident of a match. If it is not confident, it creates nothing. It can also guess wrong, so always open Model view after loading and check every line before you build a visual.

Should I set cross filter direction to Both?

Not by default. Leave relationships on Single, so filters flow from the lookup table into the table you are adding up. Microsoft recommends bi-directional filtering only as needed, because it can slow queries and create ambiguous filter paths. If one measure genuinely needs it, the CROSSFILTER function can switch it on for that measure alone.

Do I need to mark a date table in Power BI?

You must mark it if you use the classic time intelligence functions, such as TOTALYTD and SAMEPERIODLASTYEAR, or relate dates on a whole-number key like 20261019. Marking checks that the date column is unique, has no blanks and has no gaps, and it removes the hidden auto date/time table for that table.

Easy Insight is a UK consultancy for AI, web, apps and data — senior specialists only, no juniors.