Before you design a single Power BI chart, you should decide what each table means and how the tables connect. This habit is called modeling, and it saves more time than any new chart type. A model that is wrong produces charts that look confident and are wrong too.
You open Power BI Desktop, connect to a spreadsheet, and drop three charts on a page before lunch. The bars look fine. In the meeting, someone filters to one region and revenue doubles, and someone else asks why “total customers” does not match the CRM (the system your sales team uses to track customers). The room goes quiet in that special way that means the dashboard is now the problem, and the data is no longer the point. That moment is almost never a formatting issue. It is a model problem that you painted over with visuals.
This post opens the Power BI starter series. It puts the star schema, relationships, and grain ahead of pretty pages, and the next two posts cover measures that match the question and then publishing, sharing, and trust. You do not need to become a full-time data modeler. You need a habit that keeps your first real report honest. For the wider map, start at Learn. For metric definitions that travel with you, see Metrics that matter. For the warehouse SQL habits that feed BI tools, keep the SQL series nearby.
Why visuals first feels fast and fails later
Power BI makes it easy to drag fields onto a canvas, and that speed is real. The trap is treating the report page as the place where your logic lives. When totals, filters, and drill-through disagree, people argue about chart titles when they should be asking what one row means. Visuals are only views on a model, so if the model is a pile of flat exports glued together with many-to-many guesses, every new page inherits the mess.
Picture a spreadsheet with order lines, customer names, product names, and region names all in one giant table. It works for a single pivot. It fails when you want “orders per customer this month” and “units per product this month” on the same page with clean filters, because you start inventing calculated columns that paper over duplicates. Then someone refreshes with a new extract and the paper tears.
The model-first habit is simple. Before you design the first bar chart, write down the business questions, the grain of each table, and which keys connect them. Then build the smallest star that answers those questions, and only after that pick visuals that show the answers without hiding the structure.
Rule of thumb: If you cannot finish the sentence “one row in this table means…” in plain words, stop drawing and fix the grain first.
Star schema in plain English
A star schema is a layout with one or more fact tables in the middle and dimension tables around them. Facts hold events or measurements that you count or sum, such as order lines, page views, support tickets, and invoice amounts. Dimensions hold the nouns you filter and group by, such as date, customer, product, store, and campaign.
Facts tend to be long and narrow, with lots of rows, some keys, and a few measure-friendly columns. Dimensions tend to be shorter than facts and wider, with the attributes people actually read, such as product category, customer segment, and store region. The name “star” comes from a diagram with the fact in the center and the dimensions as points. You do not need perfect warehouse purity, only a separation that matches how people ask questions.
Facts answer how much, how many, and when it happened
Take fact_order_lines as an example fact. One row might mean one product line on one order, and the columns might include order_id, order_line_id, order_date_key, customer_key, product_key, quantity, and net_amount. You sum net_amount. When “orders” is the real question and not “lines,” you count distinct order_id instead.
Dimensions answer which slice
Example dimensions are dim_date, dim_customer, and dim_product. Each one has a key that matches the fact, plus attributes people use in slicers (the filter boxes on a report page). Put the readable names in the dimensions when you can, and avoid repeating them in every fact row, so that renames and hierarchies live in one place.
Why not one flat table forever?
Flat tables are fine for a throwaway exploration. They become expensive when attributes change over time, when you need one date to play two roles (an order date versus a ship date), or when several facts share the same dimensions. Stars scale because a new fact can reuse dim_date and dim_customer without rewriting every page.
Grain is the contract for every table
Grain is the meaning of one row. If two people disagree on the grain, they will disagree on every total that comes from that table, so write the grain as a sentence and do not leave it as a feeling.
| Table | Grain sentence | Good key idea |
|---|---|---|
fact_order_lines | One row = one product line on one order | order_line_id unique |
fact_orders | One row = one order header | order_id unique |
dim_customer | One row = one customer as currently defined | customer_key unique |
dim_date | One row = one calendar day | date_key unique |
Mixing order-line and order-header grain in one table is a classic double-count machine. If you sum a “shipping fee” that exists only on the order header while the table has one row per line, shipping gets multiplied across every line. Power BI will happily draw the wrong number with a beautiful label.
If you need both line metrics and header metrics, prefer two facts that share dimensions, or a careful measure design like the one the next post covers. Do not “just average it” in a visual and hope nobody notices.
Relationships: the filters you do not see
In Power BI, relationships tell the engine how filters flow from one table to another. A slicer on dim_product[Category] should filter fact_order_lines through the product key. That only works if the relationship exists, uses the right columns, and has a sensible cardinality (how many rows match on each side) and filter direction.
Cardinality in human words
- One to many, from dimension to fact, is the default you want most of the time. One customer has many order lines.
- One to one is rare for a starter model. It is sometimes a bridge or a carefully designed extension table.
- Many to many is a warning light for beginners. It is sometimes valid with a bridge table, but often it means the grain is wrong or the keys are dirty.
Filter direction
A single direction from dimension to fact is the usual starter choice. Two-way filters can solve one specific multi-fact problem, and they can also create surprise loops where a filter on one fact reshapes another fact in ways nobody can explain in a meeting. Stay with a single direction until you have a written reason for two-way and a test page that proves it.
Active versus inactive relationships
You might connect dim_date to both the order date and the ship date, but only one relationship can be active by default. The inactive one needs a DAX formula (DAX is Power BI’s formula language, covered in the next post) using USERELATIONSHIP when a measure should use the ship date. A cleaner alternative is to build two separate date tables for the two roles. Pick one pattern and document it on the report’s “About this model” page.
Power Query work versus model work
Power Query (found under Get Data and Transform) is for shaping tables before they land in the model. It renames columns, sets types, splits columns, unpivots messy exports, filters out junk rows, and can create a proper date table. The model view is for relationships, hiding fields from the report view, marking a date table, and later adding measures.
A practical split looks like this.
- In Power Query, clean the keys, remove total rows that Excel added, make sure there is one header row, type numbers as numbers, and build
dim_dateif the warehouse did not give you one. - In the model, create relationships, hide key columns from the report view, set default summarization carefully, and add measures later.
- In neither place for long, keep any business logic that belongs upstream in the warehouse or in dbt. Power BI can patch it, but patches multiply across workspaces.
If your company already transforms data in dbt or a warehouse, connect to the finished tables when you can. The star still matters in Power BI even when the warehouse already has one. Your job becomes wiring the finished star correctly, and it stops being rebuilding the universe from raw CSV files.
Worked example: a tiny sales starter model
Imagine a small retailer. Here are the business questions for version one.
- What was net revenue by month and product category?
- How many orders did we take by region?
- Who are the top customers by net revenue this year?
You do not need twenty dimensions to answer these. You need one fact at order-line grain and three dimensions.
Sample tables (toy data)
The first table is fact_order_lines, where one row means one product line on one order.
| order_line_id | order_id | order_date_key | customer_key | product_key | quantity | net_amount |
|---|---|---|---|---|---|---|
| 1001 | 500 | 20260115 | C12 | P9 | 2 | 40.00 |
| 1002 | 500 | 20260115 | C12 | P3 | 1 | 25.00 |
| 1003 | 501 | 20260116 | C7 | P9 | 4 | 80.00 |
Next comes dim_product.
| product_key | product_name | category |
|---|---|---|
| P3 | Notebook A5 | Stationery |
| P9 | Gel pen 0.5 | Stationery |
The last dimension is dim_customer.
| customer_key | customer_name | region |
|---|---|---|
| C7 | North Wind LLC | West |
| C12 | Blue Harbor Co | East |
The dim_date table needs at least the days you will filter, with columns like date_key, date, year, month_name, and year_month. Mark it as a date table in Power BI so that time calculations later have a chance to behave.
Relationships to create
dim_date[date_key]1 → *fact_order_lines[order_date_key]dim_customer[customer_key]1 → *fact_order_lines[customer_key]dim_product[product_key]1 → *fact_order_lines[product_key]
Hide the key columns from the report view so that authors pick names and categories, and not the behind-the-scenes keys. Leave quantity and net_amount visible for now. In the next post you will prefer explicit measures over the automatic sums that Power BI applies to columns.
Sanity checks before any pretty page
Build a blank page with just three card visuals, each showing a single number.
- Sum of
net_amount(expect 145.00 on the toy data) - Count of rows in
fact_order_lines(expect 3) - Distinct count of
order_id(expect 2)
Slice by the Stationery category, and the totals should still make sense. Slice by the East region, and you should see the two lines for Blue Harbor (65.00), not an inflated join. If the numbers explode when you add a dimension, your relationship or your grain is wrong, and you should fix that before you build a color theme.
A tiny DAX preview (optional now)
Even this early, one explicit measure beats the silent column defaults.
Net Revenue =
SUM ( fact_order_lines[net_amount] )Later you can put measures in a dedicated table or a clear folder. For now, create the measure, hide the raw column from the report view if your team agrees, and use the measure on the sanity cards. The next post expands this into measures shaped like real questions.
Import, DirectQuery, and where the model lives
Starter projects usually use Import, where Power BI loads a compressed copy of the data into the dataset and a refresh updates that copy. Speed for small and medium models is often excellent. DirectQuery leaves the data in the source and sends queries live on every click. That helps when the data must be near real time or is too large to import, but it brings the source’s delays and permission problems into every click.
To learn the model-before-visuals habit, Import with a controlled extract is enough. What matters more than the mode is whether your tables are modeled as a star with clear grain. A messy DirectQuery model is still a mess, while an Import star is something you can teach and test.
Common mistakes
- Using many-to-many on dirty keys. Duplicate product keys in a dimension force many-to-many or odd automatic relationships, so remove the duplicates from your dimensions first.
- Leaving the automatic date hierarchy on for every date column. Hidden date tables multiply and confuse, so prefer one marked
dim_dateand relationships you can see. - Turning on two-way filters as a default fix. They can mask a missing bridge table, so write down the question that needs them before you enable them.
- Putting header-level numbers at line grain, for example shipping, tax, or order-level discounts that get duplicated across lines without a compensating measure.
- Leaving all columns visible. Authors pick the wrong field, invent two versions of “Region,” and the model becomes a junk drawer.
- Skipping the blank sanity page. Pretty themes hide wrong totals until the executive meeting.
- Building twenty pages before you trust one metric. Scope version one to a short list of questions, and expand after the star holds.
How to practice this week
- On day 1, pick one real report you use. Write down three business questions it claims to answer, and write grain sentences for every table behind it, or for the export you can access.
- On day 2, rebuild a tiny star in a new Desktop file with one fact, a date table, and two dimensions. Skip themes, and set up only relationships and types.
- On day 3, create the three sanity cards. Break the model on purpose by duplicating a dimension key, watch the totals explode, and then fix it. That scar is the lesson.
- On day 4, hide the keys, rename fields for humans, and add a one-paragraph “About this model” text box covering the grain, the refresh source, and the owner.
- On day 5, sketch two visuals for one question. If a number disagrees with the sanity cards, the visual is guilty until proven innocent.
If your day job uses Tableau or Looker instead, the same order still holds, with grain and relationships coming before chart cosmetics. Power BI’s Model view just makes the star harder to ignore, which is a gift if you use it.
Quick recap
- Visuals are views, and a wrong model produces confident wrong charts.
- A star schema separates facts (events and measures) from dimensions (slices and labels).
- Grain is a sentence you can defend in a meeting.
- One-to-many from dimensions to facts, with a single filter direction, is the default starter pattern.
- Do sanity cards before themes, explicit measures soon after, and pretty pages last.
The next post in the series turns raw columns into measures that match the question, including filter context and a few DAX patterns you will reuse every week.
Series notes
This is Part 1 of the Power BI starter series. Related: Metrics that matter and the SQL series.
Sources
- Microsoft Learn: Understand star schema and the importance for Power BI
- Microsoft Learn: Model relationships in Power BI Desktop
- Microsoft Learn: Set and use date tables in Power BI Desktop
- Microsoft Learn: Dataset modes in the Power BI service
- Kimball Group: Dimensional modeling techniques overview
Keep going
Same lessons in your feed
Short diagrams, hooks, and weekly tutorials on Substack, Instagram, X, and Facebook.
