# Power BI, From Zero

> Take a spreadsheet from your desktop to a live, scheduled, trustworthy Power BI report - Power Query, the data model, DAX, real visualizations, and publishing done right.


---

# Power BI, From Zero

Most people meet Power BI backwards. Someone hands them a `.pbix` file, or drops them into an existing
report, and they learn by poking at buttons until something moves. That works, eventually - but it leaves
gaps you don't notice until they cost you: a report that recalculates the wrong number for last quarter, a
dashboard that quietly breaks when the source file gets renamed, a measure that's actually a calculated
column wearing a trench coat. This guide builds the tool the other way: from the ground up, so every piece
you add sits on a model you actually understand.

Power BI looks like four different products stapled together - a query editor, a modeling layer, a formula
language, and a drag-and-drop canvas - and that's roughly what it is. **Power Query** gets data in and
cleans it up. The **data model** decides how your tables relate to each other. **DAX** is the formula
language that turns rows into answers. And the **report canvas** is just the last mile - the part everyone
sees, built on everything underneath it. Learn them in that order and Power BI stops feeling like a pile of
menus and starts feeling like a pipeline: data in, shaped, modeled, calculated, shown.

The spine of this guide is one idea that shows up everywhere in analytics tooling once you know to look for
it: the **star schema** - one central table of *facts* (the numbers you measure) surrounded by *dimension*
tables (the things you slice by: date, product, region, customer). Power BI's data model is a star schema
in different clothes, and once that clicks, relationships, DAX context, and even your report design start
making sense as consequences of that one shape rather than as separate things to memorize. If you want the
concept on its own first, [The Star Schema, Explained](/guides/star-schema-explained) covers it without any
Power BI in the way - worth a detour before phase 4 if the term is new to you.

We go mental-model-first throughout: before any button or formula, you'll understand what the thing
actually *is* and why it's shaped that way. By the end you won't just have a report - you'll have one that
refreshes on a schedule, shows the right numbers to the right people, and that you'd trust with your own
name on it.

## How to read this

- **Never opened Power BI? Read 1-6 in order.** Phases 1-3 get real data into a clean shape. Phase 4 is the
  turning point - the data model - and it changes how you think about everything after it. Phases 5-6 build
  DAX from first principles instead of throwing syntax at you.
- **Come from Excel?** You already know rows, columns, and formulas like `SUM()`. The adjustment is phase
  4 (a model isn't one big flat table) and phase 5 (DAX formulas live on the *model*, not a cell - there's
  no "cell reference" to drag down). Skim 1-3, slow down from 4 onward.
- **Already building reports, want to stop guessing?** Jump to [Phase 6: Measures vs Calculated
  Columns](06-measures-vs-calculated-columns.md) - the single most common source of "why is this number
  wrong" - then keep going through time intelligence, visuals, and publishing.

## The phases

1. **[What Power BI Actually Is & Getting Set Up](01-what-power-bi-actually-is-and-getting-set-up.md)** 🟢 -
   the four layers (Query, Model, DAX, canvas), Desktop vs Service, and installing Power BI Desktop.
2. **[Connecting to Data Sources](02-connecting-to-data-sources.md)** 🟢 - Excel, CSV, databases, and web
   sources; import vs DirectQuery, and why that choice matters early.
3. **[Shaping Data with Power Query](03-shaping-data-with-power-query.md)** 🟡 - get & transform, the
   Applied Steps list, and why Power Query records *how* you cleaned the data, not just the result.
4. **[The Data Model & Relationships](04-the-data-model-and-relationships.md)** 🟡 - a star schema in different
   clothes: fact tables, dimension tables, relationships, and cardinality.
5. **[DAX From Intuition](05-dax-from-intuition.md)** 🟡 - row context vs filter context, the idea that
   makes every DAX formula make sense once it clicks.
6. **[Measures vs Calculated Columns](06-measures-vs-calculated-columns.md)** 🟡 - the distinction that
   causes most "why is this number wrong" bugs, and how to pick the right one every time.
7. **[Time Intelligence](07-time-intelligence.md)** 🔴 - `CALCULATE`, a real date table, and
   year-to-date/prior-period comparisons that don't silently lie.
8. **[Visualizations That Do Not Lie](08-visualizations-that-do-not-lie.md)** 🟡 - choosing the chart that
   matches the question, axis and scale traps, and what to leave out.
9. **[Building Reports & Dashboards](09-building-reports-and-dashboards.md)** 🟡 - pages, slicers,
   drill-through, bookmarks, and report vs dashboard (they are not the same thing).
10. **[Publishing & Sharing (Workspaces, Apps, RLS)](10-publishing-and-sharing-workspaces-apps-rls.md)** 🔴 - workspaces, Apps,
    and row-level security so the right person sees only their own data.
11. **[Refresh, Gateways & Capacity Basics](11-refresh-gateways-and-capacity-basics.md)** 🔴 - scheduled
    refresh, the on-premises data gateway, and what capacity limits mean for you in practice.

> Power BI's DAX engine and the star schema underneath it aren't unique to Power BI - the same shape
> powers Tableau, Looker, and most modern warehouses. Learn it here and it transfers.


---

# What Power BI Actually Is & Getting Set Up

If you've only ever seen Power BI over someone's shoulder, it probably looked like Excel that grew extra chart types. That's the wrong mental model, and it's the one that trips people up for months. Power BI is not a charting tool. It's a small, self-contained analytics database with a report sitting on top of it - and once that clicks, everything else in this guide (relationships, DAX, refresh) stops feeling like arbitrary rules and starts feeling like the obvious way a database-with-a-report-on-top would have to work.

## The mental model: three layers, one file

Every Power BI report, no matter how simple, is really three layers stacked on top of each other:

1. **Get & transform (Power Query).** You pull data in from somewhere - a CSV, a SQL database, a SharePoint list - and clean it up: rename columns, fix types, remove junk rows. This happens *before* anything is stored.
2. **The data model.** The cleaned tables land in an actual in-memory database, compressed and columnar (it's called **VertiPaq**, and you'll never type that word again after this paragraph, but knowing it exists explains why a 10-million-row table can open instantly). You define how tables relate to each other here.
3. **Visuals and DAX.** Charts, tables, and cards sit on top and query that in-memory database live, using a formula language called DAX to do the summing, filtering, and comparing.

Here's the part that matters most: **layers 1 and 2 happen once (or on a schedule), and layer 3 happens every time someone looks at the report.** When you click a bar in a chart, Power BI isn't recalculating a spreadsheet - it's firing a query against that compressed in-memory database and getting an answer back in milliseconds. That's why Power BI reports stay fast even over millions of rows, where the equivalent Excel workbook would grind to a halt: Excel recalculates formulas cell by cell; Power BI's engine was built from day one to scan and aggregate huge columns of numbers fast.

This also explains the thing that confuses every Excel refugee: **a Power BI file (`.pbix`) is not "the data with some charts drawn on it."** It's a snapshot of a real database, plus the report that queries it, plus the transformation steps that built it. Change the source data and hit refresh, and the whole thing - model and all - rebuilds from scratch, deterministically, using the steps you defined. Nothing is hand-edited. That determinism is what makes a Power BI report something you can trust and schedule, instead of something you have to babysit.

## Power BI Desktop vs. the Power BI Service

You'll hear "Power BI" used to mean two different things, and separating them now saves confusion later:

| | **Power BI Desktop** | **Power BI Service** |
|---|---|---|
| What it is | A free Windows app you install | A cloud website (app.powerbi.com) |
| What you do there | Build: connect to data, shape it, model it, design reports | Publish, share, schedule refresh, set permissions |
| Cost | Free, no account needed to build | Free tier exists; sharing with others usually needs a Pro/Premium license |
| Where you'll live in this guide | Phases 1-9 | Phases 10-11 |

Think of Desktop as your code editor and the Service as your deployment target. You build and iterate in Desktop, then publish to the Service when the report is ready for other people to see. This guide builds everything in Desktop first and only reaches the Service in phase 10, when publishing and sharing actually matters.

## Installing Power BI Desktop

Power BI Desktop only runs on Windows (there's no native Mac version - Mac users typically run it in a Windows VM or use the Service's browser-based editing for light work). Installation is genuinely uneventful:

1. Go to **powerbi.microsoft.com/desktop** and download it, or
2. Open the **Microsoft Store** on Windows and search "Power BI Desktop."

The Store version is the better default: it updates itself automatically, and Power BI ships monthly feature updates, so you want that on autopilot rather than something you remember to do by hand.

You do **not** need a Microsoft 365 subscription, a work email, or an account of any kind to install Desktop and start building reports with local files. You'll only be prompted to sign in when you try to publish to the Service or connect to an organizational data source later on.

## A first look around

Open Power BI Desktop and you'll land on a mostly blank canvas with a small stack of icons running down the left edge. Three of them are your three layers from before, made literal:

- **Report view** (the default view) - where you drag visuals onto a canvas. This is what people picture when they hear "Power BI."
- **Data view** - a spreadsheet-style look at the actual rows sitting in your model, after Power Query has processed them. Useful for sanity-checking that a column really contains what you think it does.
- **Model view** - a diagram of your tables and how they're connected to each other. This is where phase 4 will spend most of its time.

Recent versions add a fourth icon, **DAX query view**, for writing and running DAX queries directly against the model. You can ignore it for now; it isn't part of the three-layer mental model and you won't need it in this guide.

There's a fifth, easy-to-miss entry point: **Get Data**, front and center on the Home ribbon. This is where Power Query lives - it's the door into layer 1, and it's the very first thing you'll click in phase 2.

Before moving on, click around Report, Data, and Model view a few times, even with nothing loaded yet. The goal isn't to learn buttons - it's to get the three-layer model to stop being an idea on a page and start being three tabs you can point at.

## Why the "spreadsheet with extra steps" instinct leads you astray

It's worth stating plainly, because it will save you real pain later: if you treat Power BI like Excel - typing formulas directly into cells, hard-coding a number here and there to make a chart look right - you will hit a wall the moment your data needs to be refreshed, or the moment two people need to look at the same report and get the same answer. Every value on a Power BI report should trace back to a repeatable step: a Power Query transformation, a relationship, or a DAX formula - never a manual edit. That's not a style preference. It's the difference between a report you can refresh with one click in a year and one you'll be secretly afraid to touch.

Keep that rule in your back pocket. Every phase from here builds toward it: get the data in cleanly (phase 2-3), model it correctly (phase 4), calculate with DAX instead of hard-coded numbers (phases 5-7), and only then does the visual layer even enter the picture (phase 8-9).

## Recap

1. Power BI is a small in-memory analytics database (Power Query loads it, VertiPaq stores it) with a live-querying report layer on top - not a spreadsheet with nicer charts.
2. **Desktop** is where you build (free, install locally); the **Service** is where you publish and share (needs a license to share with others).
3. Report, Data, and Model view are the three layers of the mental model made into clickable tabs.
4. The rule that will save you months of pain: every number on the report should come from a repeatable step, not a manual edit.

## Quick check

Test yourself on the idea that matters most here - that Power BI is a live-querying database, not a spreadsheet with charts drawn on it:

```quiz
[
  {
    "q": "When you click a bar in a Power BI chart to filter the report, what actually happens?",
    "choices": [
      "Power BI recalculates every cell in the underlying spreadsheet, like Excel would",
      "Power BI fires a query against the compressed in-memory model (VertiPaq) and gets an answer back",
      "Nothing - visuals are static images generated once at publish time",
      "Power BI re-runs the Power Query steps from scratch to rebuild the data"
    ],
    "answer": 1,
    "explain": "Layer 3 (visuals) queries the already-built in-memory model live; it doesn't touch Power Query or recalculate cell by cell the way Excel does, which is why it stays fast even over millions of rows."
  },
  {
    "q": "You want to build a Power BI report using only a local CSV file, on your own machine. Do you need a Microsoft 365 subscription or account?",
    "choices": [
      "Yes, Power BI Desktop requires sign-in before you can open it",
      "No - Desktop needs no account for building with local files; sign-in is only required to publish to the Service or connect to organizational data",
      "Only if the CSV has more than a few thousand rows",
      "Yes, but only a free Microsoft account, not a paid subscription"
    ],
    "answer": 1,
    "explain": "Desktop is free to install and use for local work with no account; the Service (and organizational data sources) is where sign-in comes into play."
  },
  {
    "q": "A number on your report looks slightly wrong, and the deadline is in ten minutes. Why is manually typing over the value in a text box a bad fix, even if it's tempting?",
    "choices": [
      "It isn't a bad fix - as long as the number looks right now, how you got there doesn't matter",
      "It breaks the report's determinism: every value should trace back to a Power Query step, a relationship, or a DAX formula, or the report can't be trusted or refreshed safely",
      "Power BI doesn't allow manual text edits on top of visuals at all",
      "It only matters for reports with more than one page"
    ],
    "answer": 1,
    "explain": "A Power BI report is supposed to rebuild identically from its source data and defined steps every time you hit refresh; a hand-edited value breaks that guarantee and turns the report into something you have to babysit instead of trust."
  }
]
```


---

# Connecting to Data Sources

Before you can shape data, model it, or write a single DAX formula, Power BI needs to know two things: *where* your data lives, and *how* it should talk to it. That second part is the one beginners skip past, and it's the one that comes back to bite them. The connector you pick and the mode you connect in decide, right at the start, how fast your report will feel, whether it shows live numbers or a snapshot, and how much you can reshape the data later. Get this phase right and everything downstream is easier.

## What "connecting" actually means

**What it actually is.** Connecting means Power BI reaches out to a source system (a file, a database, a web service) and asks two questions: "what data do you have?" and "how do I fetch it?" It doesn't necessarily copy that data into itself right away - what happens next depends entirely on the mode you choose. This is a genuinely different idea from opening a file in Excel, where the data is just *there* the moment you open it. In Power BI, "connect" and "load the data in" are two separate steps, and understanding that split is the key to this whole phase.

**Why this exists.** Real organizations don't keep their data in one tidy spreadsheet. It's scattered across Excel files on someone's laptop, a SQL Server database the IT team runs, a folder of CSV exports, a SaaS tool's API. Power BI's job is to be the one tool that can reach into all of them without you learning a different language for each. Under the hood, every connector - Excel, SQL Server, a REST API, a SharePoint list - funnels into the same engine: Power Query, which you'll meet properly in the next phase. Connecting is just Power Query's front door.

## Get Data: the front door

Everything starts at **Home → Get Data** in Power BI Desktop. You'll see a search box and a long list of connectors, grouped by category: Files (Excel, CSV, PDF, folder), Database (SQL Server, PostgreSQL, MySQL, Oracle...), Power Platform, Azure, Online Services (SharePoint, Salesforce, Google Analytics...), and a catch-all "Other" that includes Web and Blank Query. There are well over 100 connectors, but you'll live in maybe five of them: Excel, a database connector, Web, Folder, and SharePoint.

Picking a connector isn't just picking a file format. Each connector knows the *shape* of its source - a SQL connector can ask a database for just one table's schema without pulling any rows yet; a Web connector has to actually fetch the page or API response to see what's there. That difference matters once you're connecting to something large: some connectors let you preview cheaply, others don't.

## The real fork in the road: Import vs DirectQuery vs Live Connection

This is the decision that actually deserves your attention in this phase - more than which specific connector you pick.

**What it actually is.** When you connect to most sources, Power BI asks how you want the data to reach your report:

- **Import** - Power BI copies the data into its own compressed in-memory model, right now. Your report queries that copy, not the original source.
- **DirectQuery** - Power BI stores no data. Every time a visual needs numbers, it sends a live query back to the source and waits for the answer.
- **Live Connection** - a special case, used with Analysis Services / an existing Power BI semantic model (renamed from "dataset" in late 2023, so older docs and menus may still say dataset): you connect to someone else's already-built data *model*, not raw tables, and can't add your own new tables to it.

**Why this exists.** These aren't three flavors of the same thing - they trade off completely different things:

| | Import | DirectQuery |
|---|---|---|
| Speed | Very fast - queries hit an in-memory model | As slow as the source database |
| Freshness | A snapshot, only as fresh as your last refresh | Always current, live at click time |
| Data size | Limited by available RAM / your Power BI capacity | Limited only by the source database |
| Power Query transforms | Full power, all steps run at refresh time | Restricted - fewer transforms allowed |
| Offline use | Works without a live connection to the source | Needs the source reachable at all times |

**Why people get this wrong.** DirectQuery *sounds* better - "always live data!" - so beginners reach for it by default. In practice, most reports should default to **Import**. It's dramatically faster (Power BI's engine is built to crunch millions of rows in memory), it works offline, and it doesn't hammer a production database every time someone opens a dashboard. Reach for DirectQuery only when the data is too big to import, changes by the second, or a security policy forbids copying it out of the source. You'll set up scheduled refresh (phase 11) to keep an Import model current - that's the normal way Power BI handles "I want fresh numbers," not DirectQuery.

💡 **Key point.** You can mix modes across tables in one model (**Composite mode**), but that's an advanced move - as a beginner, pick Import for the whole report unless you have a specific reason not to.

## A worked example: connecting to an Excel workbook

Say you have `Sales.xlsx` with a `Transactions` sheet. Walk through it:

1. **Home → Get Data → Excel workbook**, browse to the file.
2. The **Navigator** window opens, showing every sheet and named table in the file with a checkbox and a live preview pane on the right.
3. Tick `Transactions`. The preview confirms it looks like the table you expect - headers in the first row, no stray merged cells.
4. Two buttons: **Load** and **Transform Data**. Click **Transform Data**, not Load, almost every time - it opens Power Query Editor *before* anything lands in your model, so you can fix column types, remove a junk header row, or split a column first. Loading straight in and cleaning up after is the harder way to work.

*What just happened:* Power BI didn't load anything until you told it to. The Navigator step only reads the file's *structure* (sheet names, a preview) - the actual import happens when you hit Load or Transform Data. That lazy behavior is Power Query underneath, and it's why you can connect to a source with a billion rows and still get an instant preview: it only asks for a sample.

## A worked example: connecting to a database

For SQL Server: **Get Data → SQL Server database**, enter the server name and (optionally) a database name, then choose **Import** or **DirectQuery** right there in the dialog - this is the one moment that choice gets made. You'll then hit a credentials prompt: Windows auth, a database login, or an organizational account, depending on how the server's configured. Once connected, the Navigator shows every table and view in the database - tick the ones you need, or write a custom SQL query in **Advanced options** if you only want a specific slice (useful for cutting a huge table down before it ever reaches Power BI).

```sql
-- example custom query, pasted into Get Data's Advanced options
SELECT OrderID, CustomerID, OrderDate, Total
FROM Orders
WHERE OrderDate >= '2024-01-01'
```

*What just happened:* that query runs on the SQL Server itself, and only the results come back to Power BI - filtering at the source instead of importing everything and filtering later is almost always faster.

## Connecting to a web source

**Get Data → Web**, paste a URL. For a public API returning JSON, Power BI fetches the response and, if it's structured data, offers to turn it straight into a table. For a plain webpage, it detects any HTML tables on the page and lets you pick one from the Navigator - genuinely useful for pulling, say, a public data table off a government site without copy-pasting cells by hand.

⚠️ **The "it worked once, now it's broken" trap.** A Web or folder connection depends on something outside Power BI's control - a URL staying valid, a folder keeping the same file names. When a refresh suddenly fails, check the source first before you suspect Power BI. This is also why credentials and privacy levels (Home → Data source settings) matter: a connection that worked interactively on your machine can still fail on a scheduled refresh if the stored credentials expired or the privacy level blocks combining that source with another.

## Recap

1. **Connecting is a two-part question**: which connector, and which mode (Import, DirectQuery, or Live Connection) - the mode decides speed, freshness, and how you can transform the data later.
2. **Default to Import.** It's faster, works offline, and you refresh it on a schedule rather than querying live. Reach for DirectQuery only when the data's too big or too live to copy.
3. **Get Data → Navigator → Transform Data** is the standard path - preview first, clean in Power Query before it ever loads, don't clean up after.
4. **Push filtering to the source where you can** (a custom SQL query, a Web URL that's already scoped) - it's faster than importing everything and trimming it down inside Power BI.
5. Credentials and data source settings live outside the report file itself - a broken scheduled refresh is often a credentials or source problem, not a Power BI bug.

## Quick check

Test yourself on the idea that matters most in this phase - that the connection mode you pick decides how the report behaves, not just how it connects:

```quiz
[
  {
    "q": "A beginner says: \"DirectQuery is the better default - it's always live, so why would I ever pick Import?\" What's wrong with that reasoning?",
    "choices": [
      "Import is dramatically faster (queries hit an in-memory copy), works offline, and covers 'fresh data' via scheduled refresh - DirectQuery should be reserved for data too big or too live to copy",
      "Nothing - DirectQuery should always be the default for every report",
      "DirectQuery is only for Excel files, so it doesn't apply to most sources anyway",
      "Import can't be refreshed once loaded, so it's always stale"
    ],
    "answer": 0,
    "explain": "DirectQuery sounds better because it's always current, but every visual has to wait on a live query to the source, which is as slow as that source. Import copies the data into a fast in-memory model and you keep it current with scheduled refresh - that's the normal path, not DirectQuery."
  },
  {
    "q": "You click Get Data → Excel workbook and the Navigator window opens showing your sheets with a preview. Has Power BI loaded your data into the report yet?",
    "choices": [
      "No - the Navigator only reads the file's structure and a preview sample; the actual import happens when you click Load or Transform Data",
      "Yes - opening the Navigator already copies the full sheet into the model",
      "Yes, but only the first row of each sheet is loaded at this point",
      "No - nothing happens until you close and reopen Power BI Desktop"
    ],
    "answer": 0,
    "explain": "Connecting and loading are two separate steps. The Navigator step only asks the source what it has and shows a preview - it doesn't pull the real data in until you tell it to, which is why you can preview a source with a billion rows instantly."
  },
  {
    "q": "In the Navigator, why click Transform Data instead of Load when connecting to a new source?",
    "choices": [
      "Transform Data opens Power Query Editor before anything lands in the model, so you can fix column types or remove a junk header row before it loads - cleaning up after Load is the harder way to work",
      "Load and Transform Data do the same thing; the button choice is just cosmetic",
      "Transform Data is required for database connectors and skipped for files",
      "Load discards the data permanently, while Transform Data keeps a backup copy"
    ],
    "answer": 0,
    "explain": "Transform Data gets you into Power Query before the data is committed to the model, so fixes happen at the source of the pipeline instead of being bolted on afterward. Loading first and cleaning later just means redoing that work under worse conditions."
  }
]
```


---

# Shaping Data with Power Query

Phase 2 got data into Power BI. This phase is about the step almost nobody's spreadsheet is ready for on arrival: real data has merged header rows, a "Q1 Sales" column next to a "Q2 Sales" column instead of a proper date, trailing spaces, numbers stored as text, and three duplicate rows nobody noticed. Power Query is where you fix all of that - and the mental model behind it is the thing that makes everything downstream (the model, DAX, refresh) actually trustworthy.

## The mental model: a recorded script, not a one-time cleanup

**What it actually is.** When you click "Remove Duplicates" or "Split Column" in the Power Query Editor, Power BI doesn't quietly change your data once. It appends a step to a list, visible in the **Applied Steps** pane on the right, and writes that step in a language called **M**. Every transform you've ever clicked is sitting there as an ordered, named, editable step.

**Why this exists.** Your source data is never touched. Power Query reads it fresh every time you hit Refresh, then replays the *entire list of steps* from scratch, in order. That's what "repeatable" from phase 1 actually means in practice: you're not cleaning a spreadsheet once, you're writing a small program that cleans *whatever the source looks like next month*, automatically, the same way every time.

This has a very practical consequence: **order matters, and steps can break.** If you rename a column in step 3 and a later step still refers to the old name, refresh fails with a red error - not a silent wrong answer. That's a feature, not a bug. It's the same "compiler catches it before it ships" trade you saw with Power BI's determinism in phase 1, just applied to data cleaning instead of a formula.

📝 **Terminology.** The **Applied Steps** pane lists your transforms top to bottom, each one a checkpoint you can click to preview the data *at that point*. The **Advanced Editor** (Home → Advanced Editor) shows the same steps as raw M code - useful once you want to see what a click actually generated, or to hand-edit something the UI can't reach.

## A worked example: from wide export to tidy table

Say a source export looks like this - one row per product, one column per month, the classic shape a spreadsheet produces and a data model can't use directly:

| Product | Jan | Feb | Mar |
|---|---|---|---|
| Widget | 120 | 95 | 140 |
| Gadget | 60 | 70 | 55 |

Walk through the transforms that get this into model-ready shape.

**1. Fix data types first.** Every column has a little type icon (`ABC`, `123`, calendar) in its header. Power Query often guesses wrong - a `Product Code` column of `001`, `002` gets auto-detected as a number and silently drops the leading zero. Set types explicitly, right after import, before anything else touches the column. This one habit prevents a disproportionate share of "why is my total wrong" bugs later.

**2. Unpivot the month columns.** This is the single most useful transform in Power Query, and the one most beginners don't know exists. Select the `Product` column (the one you want to *keep*), right-click → **Unpivot Other Columns**, and Power Query turns your wide table into a tall one:

| Product | Attribute | Value |
|---|---|---|
| Widget | Jan | 120 |
| Widget | Feb | 95 |
| Widget | Mar | 140 |
| Gadget | Jan | 60 |
| Gadget | Feb | 70 |
| Gadget | Mar | 55 |

Rename `Attribute` to `Month` and `Value` to `Sales`, and you now have one row per fact - exactly the shape a data model (and DAX) wants. A wide table can't grow new months without a schema change; a tall one just gets more rows. This is also why a fresh export next quarter with a `Apr` column added doesn't break anything downstream - because you pinned `Product` and melted *everything else*, unpivot picks up whatever month columns exist. (Had you instead selected the months and chosen plain **Unpivot Columns**, Power Query would hardcode that `Jan`/`Feb`/`Mar` list, and `Apr` would arrive as a stray un-melted column.)

**3. Split, trim, and replace.** `Split Column by Delimiter` breaks `"Smith, John"` into two columns on the comma. `Trim` and `Clean` (under Transform → Format) strip stray whitespace and non-printing characters that make two visually-identical values fail to match later. `Replace Values` fixes known bad data - `"N/A"` becoming a real null, for instance.

**4. Filter rows and remove duplicates early.** Do this near the top of your steps, not the bottom. Every step downstream processes fewer rows if junk rows are gone first - and on large sources, this can be the difference between a five-second refresh and a five-minute one.

## Merge vs. Append: the two ways to combine queries

These get confused constantly because both involve two queries, but they answer completely different questions.

| | **Merge** | **Append** |
|---|---|---|
| Answers | "Add *columns* from another table" | "Add *rows* from another table" |
| SQL equivalent | JOIN | UNION ALL |
| Use it when | You have a `Sales` table and a `Products` table and want product names on the sales rows | You have `Sales_Jan.csv` and `Sales_Feb.csv` and want them as one table |
| Needs | A matching key column in both tables | The same column structure in both tables |

**Merge** is how you'd bring a lookup table's columns onto your fact rows *before* modeling - though more often (as phase 4 covers) you leave tables separate and use a relationship instead of merging, because a relationship stays live and a merge is a one-time flattening. **Append** is exactly what you want for "twelve monthly export files that are really one table" - point Power Query at the folder, and Append stacks them.

## Seeing the M code, and why query folding matters

Open the Advanced Editor on any query and you'll see something like this - the actual program your clicks wrote:

```m
let
    Source = Csv.Document(File.Contents("C:\data\sales.csv")),
    ChangedType = Table.TransformColumnTypes(Source,
        {{"Product", type text}, {"Jan", Int64.Type}, {"Feb", Int64.Type}}),
    Unpivoted = Table.UnpivotOtherColumns(ChangedType, {"Product"},
        "Month", "Sales")
in
    Unpivoted
```

Each `let` line is one Applied Step, in order, each one built on the last. You never need to write M by hand for ordinary cleanup - the UI generates this for you - but recognizing it removes the mystery, and it's the only way to fix the rare step the UI itself can't undo cleanly.

**Query folding** is the performance idea worth knowing early: when your source is a real database (SQL Server, Postgres, a warehouse), Power Query tries to translate your steps into a single query and run it *on the source*, pulling back only the final, already-filtered, already-aggregated result. Filter, remove-columns, and group-by steps usually fold. A custom M function, or a step that mixes two different data sources, usually doesn't - and once folding breaks, every step after it runs locally, row by row, in Power BI instead of on the server. You can check whether a step still folds by right-clicking it: if "View Native Query" is available, it's still folding. For small local files this never matters. For a multi-million-row database table, it's the difference between a refresh that takes seconds and one that times out.

## Recap

1. Power Query records every transform as an ordered, named **Applied Step**, written in **M**, replayed in full on every refresh - your source data is never touched.
2. Set data types explicitly and early; a wrong auto-detected type is the most common silent-bug source.
3. **Unpivot** turns wide spreadsheet exports (one column per period) into the tall, one-row-per-fact shape a data model needs.
4. **Merge** adds columns (a join); **Append** adds rows (a union) - don't confuse them.
5. **Query folding** pushes your steps back to the source database when possible; it's why the same transforms can be instant on a database and slow on a giant local file.

## Quick check

Test yourself on the two ideas that trip people up most: what refresh actually does, and Merge vs. Append.

```quiz
[
  {
    "q": "When you hit Refresh in Power BI, what does Power Query actually do?",
    "choices": [
      "Re-reads the source fresh and replays every Applied Step from scratch, in order",
      "Directly edits and overwrites the original source file with the cleaned version",
      "Applies only the newest step to the data that's already loaded",
      "Skips any step that didn't change the row count last time"
    ],
    "answer": 0,
    "explain": "Your source is never touched - refresh re-reads it and replays the entire ordered list of Applied Steps every time, which is why a renamed column upstream can break a later step."
  },
  {
    "q": "A monthly export has one column per month (Jan, Feb, Mar...). Why is Unpivot the fix, rather than just leaving it as-is?",
    "choices": [
      "It deletes any column that contains duplicate values",
      "It turns the wide, one-column-per-period layout into a tall, one-row-per-fact table, so a new month just adds rows instead of needing a new column",
      "It automatically detects and fixes wrong data types",
      "It merges the export with a lookup table"
    ],
    "answer": 1,
    "explain": "A data model wants one row per fact. Unpivot reshapes wide-by-period data into that tall shape, so next quarter's new column doesn't require touching the query at all."
  },
  {
    "q": "You have a Sales table and a Products table sharing a ProductID column, and you want product names added onto each sales row. Merge or Append?",
    "choices": [
      "Append, because you're combining two tables into one",
      "Merge, because you're adding columns from another table based on a matching key",
      "Unpivot, because the two tables have different shapes",
      "Either works the same way here"
    ],
    "answer": 1,
    "explain": "Merge is a join - it adds columns using a matching key. Append is a union - it stacks rows from tables with the same columns, like twelve monthly files that are really one table."
  }
]
```


---

# The Data Model & Relationships

You just spent Phase 3 shaping data in Power Query - cleaning columns, splitting fields, fixing types. It's tempting to load one big clean table into Power BI and call it done. That instinct is wrong, and understanding why is the single biggest jump in skill this guide asks of you. Get this phase right and DAX (Phase 5) will feel like common sense. Skip it and you'll spend months fighting numbers that are silently wrong.

## The mental model: a model is several small tables, not one big one

**What it actually is.** A Power BI data model is a set of tables connected by relationships, not one flat spreadsheet. You'll typically have one table that records *events* - a sale, an order, a shift, a click - with one row per event, and several smaller tables that describe the *things involved* in those events: customers, products, dates, stores. The event table is called a **fact table**. The description tables are called **dimension tables**. This is a **star schema** - the same pattern covered end to end in [Star Schema, Explained](/guides/star-schema-explained), just wearing a Power BI costume. If you haven't read that guide, the short version: one fact table in the middle, dimension tables around it like points of a star, joined by keys.

**Why this exists.** A flat spreadsheet with "Customer Name," "Customer City," "Product Name," "Product Category," and "Sale Amount" all in one row per sale looks simpler, but it repeats "New York" and "Electronics" on every single row that mentions them. That repetition isn't just wasteful - it's actively dangerous the moment you try to count something. "How many customers do we have?" against a flat table means counting distinct names across millions of repeated rows, which is slow and error-prone the instant a name is spelled two different ways. Split the customer into its own table with one row per customer, and "how many customers" becomes "how many rows in that table." Counting becomes correct *by construction*, not by careful filtering.

**Why people get this wrong.** Excel trained you to want one sheet with everything in it, because Excel has no real concept of relationships between sheets - VLOOKUP is you doing the join by hand, every time. Power BI has relationships built into the engine, so the instinct to flatten everything into one table is actively working against the tool. Let go of the flat-sheet habit; it's the single most common reason new Power BI users get numbers that don't add up.

## Fact tables and dimension tables, concretely

Say you're modeling coffee shop sales. A flat version might look like this:

```text
date       | customer   | city     | product   | category  | qty | amount
2026-07-01 | J. Osei    | Accra    | Latte     | Beverage  | 2   | 9.00
2026-07-01 | J. Osei    | Accra    | Croissant | Bakery    | 1   | 3.50
2026-07-02 | M. Chen    | Nairobi  | Latte     | Beverage  | 1   | 4.50
```

Split into a star, that becomes four tables:

```text
Sales (fact)                          Customer (dimension)
date       | cust_id | prod_id | qty | amount    cust_id | name    | city
2026-07-01 | 101     | 501     | 2   | 9.00      101     | J. Osei | Accra
2026-07-01 | 101     | 502     | 1   | 3.50      102     | M. Chen | Nairobi
2026-07-02 | 102     | 501     | 1   | 4.50

Product (dimension)                   Date (dimension)
prod_id | name      | category         date       | month   | quarter | year
501     | Latte     | Beverage         2026-07-01 | July    | Q3      | 2026
502     | Croissant | Bakery           2026-07-02 | July    | Q3      | 2026
```

*What just happened:* `Sales` keeps only the numbers that change every row - quantity, amount - plus IDs pointing at the tables that describe *who*, *what*, and *when*. "City" lives in `Customer` exactly once per customer, not once per sale. If a customer moves city, you fix one row, not thousands.

📝 **Terminology.** The column a relationship joins on is a **key** - `cust_id`, `prod_id`, `date`. On the dimension side it's usually unique per row (the **one** side). On the fact side it repeats once per transaction (the **many** side). That "one row describes many events" shape is exactly what a relationship encodes.

## Building the relationship

Power BI's **Model view** (the third icon down the left rail, after Report and Data) is where you see your tables as boxes and draw lines between them. Drag `cust_id` from `Sales` onto `cust_id` in `Customer` and Power BI draws a line with a `1` at the `Customer` end and a `*` at the `Sales` end.

```text
Customer (1) ──────< (*) Sales
Product  (1) ──────< (*) Sales
Date     (1) ──────< (*) Sales
```

*What just happened:* that `1` to `*` line is Power BI's version of a foreign key join, except it's not run per query like a SQL join - it's a standing relationship the engine uses every time a visual asks for data. Put "City" on a chart's axis and "Amount" as a value, and Power BI silently walks the relationship from `Customer` to `Sales`, filters `Sales` down to the rows for that city, and sums `Amount`. You never write the join. You just build the model once, correctly, and every visual afterward gets it for free.

## Cardinality: what kind of relationship is this

**What it actually is.** Cardinality describes how many rows on each side of a relationship can match. Power BI shows three kinds:

| Cardinality | Meaning | Example |
|---|---|---|
| One-to-many (1:*) | One dimension row matches many fact rows | One customer, many sales |
| One-to-one (1:1) | Exactly one row matches exactly one row on both sides | A customer and their loyalty-card record |
| Many-to-many (*:*) | Many rows on each side can match many on the other | Products and the promotions that can apply to several products each |

**Why this matters.** One-to-many is the normal, healthy shape of a star schema, and it's what Power BI expects by default. Many-to-many is real (it comes up more than beginners expect - shared bank accounts, students in multiple classes, products in overlapping promotions) but it changes how filters propagate and is easy to build by accident when a "dimension" table actually has duplicate keys. If Power BI reports a relationship as many-to-many and you expected one-to-many, that's a signal your dimension table isn't as unique as you thought - go check for duplicate keys before you trust anything downstream.

## Cross-filter direction: which way filtering flows

**What it actually is.** Every relationship has a cross-filter direction: **single** (the default for the one-to-many relationships a star schema is built from) means filtering flows only from the "one" side to the "many" side - filter `Customer` by city, and `Sales` filters down accordingly, but filtering `Sales` does *not* filter `Customer` back. **Both** lets filtering flow in both directions.

**Why this exists.** Single direction matches how a star schema is meant to be queried: dimensions filter facts, not the other way around, and that keeps the model predictable. "Both" can look convenient - it makes a relationship "just work" in more scenarios - but it can also silently create ambiguous filter paths once you have more than a couple of tables connected, where Power BI can't tell which route a filter should take and either refuses the relationship or gives you a number that's technically correct but not what you meant.

**Rule of thumb.** Leave cross-filter direction as single unless you hit a specific, understood case that needs "both" - don't flip it defensively because a number looks wrong. A wrong number is almost never fixed by loosening the model; it's fixed by finding the actual mistake (often a missing relationship or the wrong table granularity).

## A dedicated Date table, always

Power BI can auto-generate hidden date hierarchies from any date column, but build your own `Date` table anyway and relate it to every fact table's date column. Time intelligence (Phase 7 - things like "sales this month vs. last month") depends on DAX functions that expect a proper, continuous, marked-as-a-date-table `Date` dimension. Skipping this now means redoing your whole model later.

## Recap

1. A Power BI model is several small related tables, not one flat sheet - fact tables hold events and numbers, dimension tables hold the descriptive detail, in the same star-schema shape as [Star Schema, Explained](/guides/star-schema-explained).
2. Relationships join tables on a key, usually one dimension row (**1**) to many fact rows (**\***) - build them once in Model view and every visual reuses them, no manual joins.
3. Cardinality (1:*, 1:1, *:*) describes how rows match on each side; an unexpected many-to-many usually means duplicate keys in what you thought was a clean dimension.
4. Cross-filter direction controls which way filtering flows - leave it single unless you have a specific, understood reason for both.
5. Build a real `Date` table and relate it everywhere dates live; time intelligence in Phase 7 depends on it.

### Check yourself

```quiz
[
  {
    "q": "You've flattened all your sales data into one wide table, repeating customer and product details on every row. What's the real problem this causes, beyond extra storage?",
    "choices": [
      "Counting distinct things like customers becomes unreliable, because you're counting rows in a table where each customer appears many times",
      "Power BI simply refuses to load flat tables past a few thousand rows",
      "DAX functions won't run at all against a flat table",
      "Relationships become mandatory instead of optional"
    ],
    "answer": 0,
    "explain": "A flat table repeats descriptive values on every row, so 'how many customers' means counting distinct names across repeats instead of counting rows in a table built for that purpose - slow and error-prone the moment a name is spelled two ways."
  },
  {
    "q": "Power BI reports a relationship you expected to be one-to-many as many-to-many instead. What does that most likely mean?",
    "choices": [
      "The table you assumed was a clean dimension actually has duplicate keys, so it isn't as unique as you thought",
      "Nothing meaningful - many-to-many is just Power BI's default label for any new relationship",
      "The fact table doesn't have enough rows yet",
      "You need to switch the cross-filter direction to both to fix it"
    ],
    "answer": 0,
    "explain": "A one-to-many relationship requires uniqueness on the 'one' side; if Power BI sees repeats there, it reports many-to-many - that's a signal to go check for duplicate keys, not something to filter your way around."
  },
  {
    "q": "A relationship's cross-filter direction is left on the default, single, instead of switched to both. What does single actually do?",
    "choices": [
      "Filtering flows from the dimension side to the fact side only, which matches how a star schema is meant to be queried",
      "It leaves the relationship inactive until both is turned on",
      "It only applies to one-to-one relationships",
      "It's a temporary setting Power BI auto-upgrades to both as the model grows"
    ],
    "answer": 0,
    "explain": "Single direction means filtering a dimension (like Customer) filters the fact table down, but filtering the fact table doesn't filter the dimension back - the predictable shape a star schema depends on."
  }
]
```


---

# DAX From Intuition

If you've written Excel formulas, DAX looks familiar and that familiarity is a trap. `SUM`, `IF`, `AVERAGE` - the names are the same, and the first few formulas you write will just work, so you'll assume you already understand DAX. Then you'll write something that looks identical in spirit to a formula that worked yesterday, and it'll return a number that's obviously wrong, and you won't have any idea why. That gap - between "the syntax looks like Excel" and "the evaluation model is nothing like Excel" - is where almost everyone gets stuck. This phase closes that gap by building the model *before* the syntax.

## The mental model: two kinds of context

**What it actually is.** Every DAX formula is evaluated inside a *context* - a set of filters that determines which rows of your tables are "visible" when the formula runs. There are two kinds of context, and the entire language makes sense once you can tell them apart:

- **Row context** - "I am currently standing on one specific row. I can reach across that row to read its columns." This is what you get inside a calculated column, and inside iterator functions like `SUMX`.
- **Filter context** - "A set of filters is currently narrowing down which rows count." This is what you get from slicers, from rows/columns in a visual, and from filters you write yourself. This is the context a *measure* lives in.

**Why this exists.** In Excel, a formula lives in one cell and only ever sees that cell's row - there's no such thing as "the currently selected region of the report" reaching into your formula. Power BI is built to answer a different question: *"given whatever the user just clicked, filtered, or sliced, what's the total?"* That question can't be answered by a formula that only knows about one row. DAX needed a way for a single formula to be aware of "everything currently filtered" - that's filter context, and it doesn't exist in spreadsheet formulas at all.

**Why people get this wrong.** They write a measure, test it in one visual where it looks right, drop it into a table with different rows and columns, and it now returns a different (correct, but unexpected) number. That's not a bug. The measure is recalculating for *every* combination of filters the report throws at it. A measure has no fixed answer - it's a question that gets re-asked for every cell of every visual.

## Watching filter context happen

You don't have to imagine this - drop one measure into a matrix visual and watch it live:

```dax
Total Sales = SUM(Sales[SalesAmount])
```

Put this measure on a card by itself and it sums every row in the `Sales` table - no filters, no context, just the grand total. Now drag `Region` onto the rows of a table visual with `Total Sales` in the values. Suddenly you see four different numbers, one per region, and the visual didn't touch your formula at all. Power BI evaluated `Total Sales` once *per row of the visual*, each time with a different filter context: "only rows where Region = West," then "only rows where Region = East," and so on. The formula never changed. The context around it did.

This is the single biggest shift from spreadsheet thinking: **you don't write one formula per answer. You write one formula, and the report supplies the context that turns it into many answers.**

## CALCULATE: the function that changes the question

Once filter context clicks, `CALCULATE` stops being a scary function and becomes the obvious next step: it's the function that lets *you* change the filter context instead of waiting for a visual to do it.

```dax
Sales Last Year =
CALCULATE(
    [Total Sales],
    SAMEPERIODLASTYEAR('Date'[Date])
)
```

**What it actually is.** `CALCULATE` takes an expression (here, the `Total Sales` measure) and a list of filter arguments, and evaluates the expression inside a *modified* filter context - the existing filters, overridden or added to by whatever you pass in. `SAMEPERIODLASTYEAR` shifts the date filter back one year, so `Total Sales` gets computed as if the report were showing last year instead of this year, while every other filter (region, product, whatever the user picked) stays exactly as it was.

**Why this exists.** Without `CALCULATE`, a measure can only ever answer "what does the *current* filter context say?" There'd be no way to ask "what if the year were different" or "what if we ignored the product filter" from inside a formula. `CALCULATE` is the one function that reaches out and edits the question before answering it - which is why nearly every non-trivial DAX pattern (year-over-year, running totals, "% of total," excluding a filter) is `CALCULATE` wearing a different outfit.

A simpler example, filtering with a plain condition instead of a time function:

```dax
High Value Sales =
CALCULATE(
    [Total Sales],
    Sales[SalesAmount] > 1000
)
```

This computes total sales, but only counting the rows where the sale itself was over 1000 - regardless of what a slicer elsewhere on the page is doing. `CALCULATE`'s filter argument doesn't add to the existing SalesAmount filter, it replaces any filter on that column with this new one, then everything else (region, date, whatever) stays untouched.

## Row context: the other half of the story

Filter context governs measures. Row context governs a different situation: when DAX is walking through a table one row at a time.

```dax
Line Total = Sales[Quantity] * Sales[UnitPrice]
```

Add this as a *calculated column* on the `Sales` table, and DAX evaluates it once per row, and on each row `Sales[Quantity]` and `Sales[UnitPrice]` mean "the value in *this* row." That's row context - "I'm standing on a row, I can read across it." There's no filtering happening here at all; it's just arithmetic, one row at a time, exactly like dragging an Excel formula down a column.

The two contexts collide in iterator functions like `SUMX`, which is worth seeing once so it stops looking like a typo of `SUM`:

```dax
Total Revenue =
SUMX(
    Sales,
    Sales[Quantity] * Sales[UnitPrice]
)
```

**What it actually is.** `SUMX` takes a table and an expression, walks the table row by row (creating row context for each row, just like the calculated column above), evaluates the expression on each row, and adds up the results. `SUM(Sales[Revenue])` only works if a `Revenue` column already exists. `SUMX` lets you compute the same total *without* materializing that column first - it builds the per-row value and sums it in one pass. This matters because every calculated column you add makes your model bigger and slower to refresh; an iterator that computes the same number on the fly often costs you nothing extra.

## Why your first "obviously correct" measure returns the wrong number

Here's the classic trap, now that you have the vocabulary for it:

```dax
Profit Margin = ([Total Sales] - [Total Cost]) / [Total Sales]
```

This looks right and mostly is - but put it in a visual total row, or a card with no filters, and if `[Total Sales]` happens to be blank for some slice (a product with no sales this month, say), the plain `/` operator hands back `Infinity` (or `NaN` when both sides are zero) instead of a clean 0%. The fix uses a function you'll reach for constantly:

```dax
Profit Margin =
DIVIDE(
    [Total Sales] - [Total Cost],
    [Total Sales],
    0
)
```

`DIVIDE` is just division with a built-in "if the denominator is blank or zero, return this instead" - a third argument that plain `/` doesn't give you. It's a small function, but it's the difference between a report that silently breaks on an edge case and one that doesn't. The lesson underneath it is bigger than this one function though: **a DAX formula's correctness depends on the contexts it might run in, not just the logic that looks right for one case.** Always ask "what happens when this filter context is empty, or narrowed to one weird row?" - that question is where most DAX bugs live.

## What to carry forward

You now have the two ideas that everything else in DAX builds on:

- **Row context**: "I'm on one row, I can read across it" - calculated columns and the inside of iterators like `SUMX`.
- **Filter context**: "a set of filters is narrowing what's visible" - what a measure evaluates inside, driven by slicers, visuals, and `CALCULATE`.
- **`CALCULATE`** is the function that lets you rewrite the filter context yourself, which is why it underlies almost every advanced DAX pattern you'll meet.

None of this replaces knowing individual functions - you'll still look up `SAMEPERIODLASTYEAR` or `ALLEXCEPT` when you need them. But now, when a formula behaves strangely, you have a real question to ask: *"what row context or filter context is this actually running in?"* That question, more than any function reference, is what makes DAX click.

### Check yourself

```quiz
[
  {
    "q": "A measure `Total Sales = SUM(Sales[SalesAmount])` shows $40,000 on a card, then $12,000 when you drop it into a table row filtered to one region. What changed?",
    "choices": [
      "The measure's formula was silently rewritten by Power BI for that visual",
      "Nothing about the formula changed - the filter context around it changed",
      "This is a bug; a measure should always return the same number everywhere"
    ],
    "answer": 1,
    "explain": "A measure has no fixed answer - it re-evaluates inside whatever filter context the visual supplies, so the same formula legitimately returns different numbers in different places."
  },
  {
    "q": "You write `CALCULATE([Total Sales], Sales[SalesAmount] > 1000)` on a page that already has a slicer filtering `SalesAmount` to a different range. What happens to the slicer's filter on that column?",
    "choices": [
      "It stays in effect alongside the new condition, so both filters apply together",
      "CALCULATE's filter argument replaces any existing filter on that same column",
      "CALCULATE ignores the new condition and just returns the slicer's filtered total"
    ],
    "answer": 1,
    "explain": "A CALCULATE filter argument overrides the existing filter on that column rather than stacking on top of it - only filters on other columns are left untouched."
  },
  {
    "q": "Why write `DIVIDE([Total Sales] - [Total Cost], [Total Sales], 0)` instead of just `([Total Sales] - [Total Cost]) / [Total Sales]`?",
    "choices": [
      "DIVIDE runs faster because it's a native DAX function instead of an operator",
      "They're equivalent - DIVIDE is only there for readability",
      "Plain division errors or blanks out when the denominator is blank or zero; DIVIDE lets you supply a fallback"
    ],
    "answer": 2,
    "explain": "DIVIDE's third argument controls what comes back when the denominator is blank or zero, which plain `/` has no way to express."
  }
]
```


---

# Measures vs Calculated Columns

You just met DAX in the last phase and it felt like a spreadsheet formula language. It mostly is - except for one decision that trips up nearly everyone new to Power BI: the exact same-looking formula, `[Price] * [Quantity]`, behaves completely differently depending on whether you type it into a **calculated column** or a **measure**. Same syntax, different universe. This phase is entirely about that difference, because getting it wrong is the single most common source of "why is my number wrong" bugs in real Power BI reports.

## The mental model: stored value vs. live answer

**What a calculated column actually is.** A calculated column computes one value *per row*, at the moment you refresh the data, and then stores that value physically in the model - exactly like a column that came from your source data. Once it's calculated, it just sits there as data. If you open Excel and add a column `= B2 * C2` and drag it down, that is precisely the mental model: a value glued to each row.

**What a measure actually is.** A measure computes nothing until a visual asks for it, and even then it never computes "a row" - it computes one aggregated number for whatever is currently being looked at. Put a measure into a table visual sliced by Region, and it silently reruns once per region, each time asking "given only the rows visible right now, what's the answer?" Nothing is stored. A measure is a formula on standby, waiting to be asked a question shaped by whatever filters, slicers, and groupings are on screen at that instant.

That's the whole idea in one line: **a calculated column answers "what's this row's value?" once, up front. A measure answers "what's the total, right now, for whatever you've selected?" every single time you look.**

## Why the same formula gives different numbers

This is where it stops being an abstract distinction and starts mattering. Say you have a `Sales` table with `UnitPrice` and `Quantity` columns, and you want revenue.

As a **calculated column**:

```dax
Revenue Column = Sales[UnitPrice] * Sales[Quantity]
```

This runs once per row, in what DAX calls **row context** - the formula knows "the current row" the way a spreadsheet formula knows "this row." It stores a `Revenue Column` value on every single row of `Sales`, forever, until the next refresh.

As a **measure**:

```dax
Revenue Measure = SUMX(Sales, Sales[UnitPrice] * Sales[Quantity])
```

This computes nothing until a visual asks for it. Drop it into a card visual, and it runs `SUMX` over every row currently in **filter context** - the set of rows left after every slicer, page filter, and visual grouping has been applied - multiplies each one, and sums the result on the fly. Slice by Region and it reruns, using only that region's rows.

For a straight sum like revenue, both approaches land on the same final number if you total everything. But they get there completely differently, and that difference has real consequences the moment you do anything other than a plain sum.

## Where it breaks: ratios and averages

Here's the example that actually teaches the lesson. Suppose you want Profit Margin = Profit / Revenue, per product category.

**The tempting, wrong instinct** is a calculated column:

```dax
Margin Column = DIVIDE(Sales[Profit], Sales[Revenue])
```

Then in a visual, you set it to "Average" and get an average of a bunch of per-row percentages. That number is almost never the number a stakeholder actually wants, because it treats a $2 sale and a $200,000 sale as equally important to the average. This is the classic "average of ratios" trap - it's mathematically a different question than "overall margin," and the two can disagree by a lot when order sizes vary.

**The correct instinct** is a measure that sums first, divides second:

```dax
Profit Margin = DIVIDE(SUM(Sales[Profit]), SUM(Sales[Revenue]))
```

Now, no matter how the visual slices the data - by category, by month, by nothing at all - the measure always sums the numerator, sums the denominator over exactly those same rows, and divides once. That's "ratio of sums," and it's almost always what "margin" means in a real business conversation. `DIVIDE()` is DAX's safe division: it returns blank instead of erroring on divide-by-zero, so use it instead of the raw `/` operator inside measures.

This single example is why the rule of thumb exists: **ratios, percentages, and anything with an aggregation word in front of it ("total," "average," "count of") belong in a measure, not a column.**

## Context transition: turning a row into a filter

First, the everyday reason a measure written once works everywhere: dropped into a card, table, matrix, or chart axis, it simply re-evaluates against whatever **filter context** that visual hands it - the rows left after every slicer, filter, and grouping. You never rewrite it; each spot supplies a different filter context and the measure re-runs against it. That's the plain mechanism behind "it just adapts."

**Context transition** is a related trick worth knowing, because it explains behavior you'll see constantly. It kicks in specifically when a measure is evaluated inside a *row context* - inside an iterator like `SUMX`, inside a calculated column, or wrapped in `CALCULATE`. There, DAX takes "the current row" and turns it into an equivalent filter, as if you'd clicked that one row as a slicer. It's what lets you reference a measure row by row - say, pulling a `Sales` measure while iterating a `Product` table - and have each evaluation mean "just this row's rows." Calculated columns get no such magic on their own: they only ever see their own row, forever fixed at refresh time.

## So which do you actually pick?

| Use a **calculated column** when... | Use a **measure** when... |
|---|---|
| You need a new field to group, slice, or filter by (e.g. a "Price Tier" bucket from `IF(Sales[UnitPrice] > 100, "Premium", "Standard")`) | You need a total, average, ratio, count, or any other aggregation |
| The value is a genuine per-row attribute that should exist as data (e.g. pulling a related table's value with `RELATED()`) | The value should recalculate as the user slices, filters, or drills through the report |
| You'll put it on a chart axis or in a slicer | You'll put it in a card, table cell, KPI, or chart value |

The practical bias should lean hard toward measures. Calculated columns are computed at refresh and stored - which means they take up space in the model and, because high-cardinality calculated columns compress worse than columns loaded from source, they can noticeably bloat file size on large tables. Only reach for a calculated column when you genuinely need a static, row-level value to exist as a field you can group or filter by - something a measure structurally cannot do, since a measure has no rows of its own to group with. For everything else - and in a well-built report, that's the vast majority of your calculations - measures are the default, and they're what phase 7 builds on directly.

## Recap

1. A **calculated column** computes once per row at refresh time and is stored in the model, like a spreadsheet formula dragged down a column.
2. A **measure** computes nothing until a visual asks for it, then aggregates over whatever filter context is active right now - it's a live answer, not stored data.
3. Ratios and averages must be measures that sum first and divide second (`DIVIDE(SUM(...), SUM(...))`); doing the division per row in a calculated column and then averaging gives the wrong number.
4. A measure adapts to any visual because it re-evaluates against that visual's filter context. **Context transition** is the separate trick that turns "the current row" into an equivalent filter when a measure runs inside a row context (an iterator, a calculated column, or `CALCULATE`).
5. Default to measures. Reach for a calculated column only when you need a genuine per-row field to group, slice, or filter by.

Test yourself on the distinction that causes the most "why is my number wrong" bugs in Power BI:

```quiz
[
  {
    "q": "You write `Margin Column = DIVIDE(Sales[Profit], Sales[Revenue])` as a calculated column, then average it in a visual. Why is that number usually wrong?",
    "choices": [
      "It averages a per-row percentage across rows, treating a $2 sale and a $200,000 sale as equally important, instead of summing profit and revenue first",
      "Calculated columns can't use the DIVIDE() function",
      "The column only calculates for the first row and copies that value down",
      "Averaging always rounds the result down"
    ],
    "answer": 0,
    "explain": "That's the 'average of ratios' trap - a measure that sums the numerator and denominator over the same rows before dividing gives the correct 'ratio of sums' instead."
  },
  {
    "q": "A calculated column and a measure both use the formula `[Price] * [Quantity]`. What's the real difference between them?",
    "choices": [
      "The calculated column computes once per row at refresh and stores the result; the measure computes nothing until a visual asks, then aggregates over whatever filter context is active",
      "They behave identically - the only difference is where you type them",
      "A measure is just a faster calculated column",
      "A calculated column updates live as you slice a visual, while a measure is fixed at refresh"
    ],
    "answer": 0,
    "explain": "A calculated column is a stored, per-row value from refresh time; a measure is a live formula that reruns for whatever rows are currently in filter context."
  },
  {
    "q": "You need a field called \"Price Tier\" (Premium or Standard) so you can put it on a chart axis and slice by it. Should it be a measure or a calculated column?",
    "choices": [
      "A calculated column, because it's a genuine per-row attribute you need to group and filter by, which a measure has no rows of its own to do",
      "A measure, because the default should always lean toward measures",
      "Either works identically for slicing and grouping",
      "A measure, because calculated columns can't hold text values"
    ],
    "answer": 0,
    "explain": "Measures aggregate over rows and have none of their own to group with - a value you need on a slicer or chart axis has to exist as a stored per-row field, which is exactly what a calculated column is for."
  }
]
```


---

# Time Intelligence

Every business question eventually turns into a time question. Not "what were sales" but "what were sales *compared to last year*." Not "what's revenue" but "what's revenue *so far this quarter*." These questions feel like they need a pile of date math - filter to last year, shift the range, subtract. In Power BI they don't. They need one thing you build once (a real date table) and a small vocabulary of DAX functions that all do the same trick: **take the filter context you already have, and move it in time.**

That's the mental model for this whole phase. Time intelligence functions don't compute anything new. They swap out the date filter underneath a measure you already wrote, then let that same measure run again. Get that idea solid and the function names stop being a list to memorize - they become five or six ways of saying "the same filter, shifted."

## The one thing you need first: a real date table

Time intelligence functions are not clever about dates. They don't parse your fact table's order dates and infer a calendar. They need an actual table of contiguous dates - one row per day, no gaps - related to your fact table, and they need Power BI to know it's a date table.

This is the payoff from [Phase 4: The Data Model & Relationships](04-the-data-model-and-relationships.md): your model is a star schema, and `Date` is a dimension like any other, sitting one hop from your fact table. If you skipped building one, stop here and build it first - nothing below works reliably without it.

```dax
Date = CALENDAR (DATE(2020,1,1), DATE(2026,12,31))
```

Add a few columns you'll actually use in visuals and slicers (`Year`, `Month`, `MonthName`, `Quarter`), then two things that are easy to skip and cause the most confusing bugs later:

1. **Relate it to the fact table** on the date column, one-to-many, filtering from `Date` to the fact.
2. **Mark it as a date table**: select the `Date` table in the Data pane → *Table tools* → *Mark as date table* → pick the column that's a true, unique, contiguous date (usually the `Date` column itself).

That second step is best practice, not decoration. Every measure below passes `Date[Date]` explicitly, so the functions shift the column you hand them - they don't need the flag to find a column. What marking does is tell Power BI to treat this as *the* date table: it turns off the automatic per-column date/time hierarchies that otherwise bloat your model, and guarantees your calendar is handled as one contiguous, day-grain table so the shifts land where you expect. Time intelligence can run on an unmarked table, but skipping the mark is how the subtle, hard-to-spot wrong-result bugs it's designed to prevent creep in.

⚠️ **The most common time-intelligence bug isn't a DAX mistake at all.** It's a fact table date column with gaps (no sales on Sundays, so no rows) being used directly instead of going through a proper `Date` table. `SAMEPERIODLASTYEAR` needs to know Sunday existed, even with zero sales, or last year's comparison silently drops days.

## CALCULATE is still the engine

Recall from [Phase 5: DAX From Intuition](05-dax-from-intuition.md): `CALCULATE` takes a measure and evaluates it inside a *modified* filter context. Every time intelligence function is really just `CALCULATE` with a filter argument that happens to describe a date range - written as a shorthand so you don't hand-build it yourself.

```dax
-- these two measures do the same thing
Sales PY (spelled out) =
CALCULATE (
    [Total Sales],
    SAMEPERIODLASTYEAR ( Date[Date] )
)

Sales PY (same idea) =
CALCULATE (
    [Total Sales],
    DATEADD ( Date[Date], -1, YEAR )
)
```

`SAMEPERIODLASTYEAR` and `DATEADD` both return a *table of dates* - not a number. `CALCULATE` takes that table, uses it to replace whatever date filter was already active (from a slicer, a row in a matrix, a page filter), and re-runs `[Total Sales]` inside that new filter. That's the whole mechanism. Once you see time intelligence functions as "date-table generators for `CALCULATE`," you can read any of them cold.

## The core vocabulary

You don't need all of DAX's ~35 time intelligence functions. A handful cover almost everything a business ever asks for.

| You want | Function | What it shifts to |
|---|---|---|
| Same day last year | `SAMEPERIODLASTYEAR ( Date[Date] )` | Every date, minus one year |
| N periods back/forward | `DATEADD ( Date[Date], -1, MONTH )` | Any offset, any grain (day/month/quarter/year) |
| Year-to-date | `TOTALYTD ( [Total Sales], Date[Date] )` | Jan 1 of current year through the current filtered date |
| Quarter/month-to-date | `TOTALQTD` / `TOTALMTD` | Same idea, smaller window |
| Rolling N-day window | `DATESINPERIOD ( Date[Date], MAX(Date[Date]), -365, DAY )` | Any trailing window you define |
| Whole prior year, no shift needed | `PARALLELPERIOD ( Date[Date], -1, YEAR )` | The full prior year, regardless of current filter width |

Two worked measures, side by side, show why this matters. Given a base measure:

```dax
Total Sales = SUM ( Sales[Amount] )
```

Year-to-date and the year-over-year comparison built from it:

```dax
Sales YTD =
TOTALYTD ( [Total Sales], Date[Date] )

Sales PY =
CALCULATE ( [Total Sales], SAMEPERIODLASTYEAR ( Date[Date] ) )

Sales YoY % =
DIVIDE ( [Total Sales] - [Sales PY], [Sales PY] )
```

Drop `Sales YoY %` into a matrix with `Year` and `Month` on rows, and it just works - because at every cell, the filter context is already "this month, this year," and the measure asks `CALCULATE` to re-run `[Total Sales]` one year earlier under that exact same shape. You wrote the comparison once. It's correct at the day, month, quarter, and year grain automatically, because it rides whatever grain the visual is already sliced to.

## TOTALYTD vs. the CALCULATE + DATESYTD version

You'll see two ways to write year-to-date and they're equivalent - `TOTALYTD` is shorthand for the second:

```dax
Sales YTD (shorthand) = TOTALYTD ( [Total Sales], Date[Date] )

Sales YTD (spelled out) =
CALCULATE ( [Total Sales], DATESYTD ( Date[Date] ) )
```

Reach for the spelled-out `DATESYTD` form when you need to combine it with something else inside the same `CALCULATE` - say, YTD sales for one specific product category regardless of slicer, `CALCULATE([Total Sales], DATESYTD(Date[Date]), Product[Category] = "Bikes")`. `TOTALYTD` only takes a measure and a date column; `DATESYTD` is a plain date-table generator you can mix with any other filter arguments `CALCULATE` accepts.

By default, YTD/QTD/MTD run on a January-December calendar year. If your business runs a fiscal year starting, say, in July, every one of these functions takes an optional `year_end_date` argument (`TOTALYTD([Total Sales], Date[Date], "06-30")`) - set it once and every YTD measure respects it.

## Recap

1. **Time intelligence functions don't compute new numbers - they generate a shifted date table, then hand it to `CALCULATE`**, which re-runs your existing measure under that new filter.
2. **They need a real, contiguous date table**, related to your fact table and explicitly marked as a date table, or comparisons silently go wrong.
3. **`SAMEPERIODLASTYEAR` / `DATEADD`** shift a period; **`TOTALYTD` / `TOTALQTD` / `TOTALMTD`** accumulate from the start of a period; **`DATESINPERIOD`** builds a custom rolling window; **`PARALLELPERIOD`** grabs a whole prior period regardless of how wide the current filter is.
4. Write the comparison measure once - it inherits whatever grain (day, month, quarter, year) the visual is already sliced to.

## Check yourself

Test yourself on the idea that makes the rest of this phase click - that these functions shift a filter rather than compute a number:

```quiz
[
  {
    "q": "What does a function like SAMEPERIODLASTYEAR actually do?",
    "choices": [
      "Returns a table of dates that CALCULATE uses to replace the current date filter, then reruns the measure",
      "Recomputes the measure directly against a hardcoded date range",
      "Duplicates the fact table for the prior period and sums it",
      "Tells the date table to relabel its rows as last year"
    ],
    "answer": 0,
    "explain": "Time intelligence functions generate a date table, not a number - CALCULATE takes that table, swaps out the existing filter, and reruns your measure inside it."
  },
  {
    "q": "Why can SAMEPERIODLASTYEAR silently give wrong numbers if you filter on the fact table's own order-date column instead of a proper Date table?",
    "choices": [
      "A fact table's date column usually has gaps (no rows on days with zero transactions), so shifting a year can miss days that had no sales but should still count",
      "CALCULATE only accepts columns literally named \"Date\"",
      "Fact tables are not allowed to store dates",
      "The function needs the date column sorted alphabetically first"
    ],
    "answer": 0,
    "explain": "A real date table has one contiguous row per day, including zero-sales days, so a year-back shift lands on every date it should; a fact table's date column only has rows where a transaction happened."
  },
  {
    "q": "You need last quarter's total, in full, no matter how much of the current quarter is actually selected. Which function is built for that?",
    "choices": [
      "PARALLELPERIOD - it returns the entire prior period regardless of how wide the current filter is",
      "TOTALYTD - it accumulates from the start of the current year",
      "SAMEPERIODLASTYEAR - it shifts whatever filter width is currently active back one year",
      "DATESINPERIOD - it builds a custom trailing window measured in days"
    ],
    "answer": 0,
    "explain": "DATEADD and SAMEPERIODLASTYEAR shift the same width you already have filtered, so a single selected day stays a single day a year back; PARALLELPERIOD always expands to the whole prior period."
  }
]
```


---

# Visualizations That Do Not Lie

You've built measures ([Phase 6](06-measures-vs-calculated-columns.md)), you understand how they roll up over time ([Phase 7](07-time-intelligence.md)), and the number in your DAX is correct. None of that protects you from the next step, because a *correct* number can still be shown in a way that leads someone to the wrong conclusion. A bar chart that starts its axis at 80 instead of 0 turns a 2% wobble into what looks like a landslide. A pie chart with eleven slices turns "compare these categories" into a squint-and-guess exercise. The DAX was fine. The picture lied.

This phase is not about DAX or data modeling. It's about the last six inches between a correct number and a reader's brain, and about the specific defaults in Power BI that make that gap easy to fall into.

## The mental model: match the visual to the question, not the data

Every chart answers one of a small number of question *shapes*. Before you drag a visual onto the canvas, name the shape of your question - it tells you almost everything about which chart tells it clear and which is decoration.

| Question shape | Example | Right family |
|---|---|---|
| How does this compare across categories? | Revenue by region | Bar / column chart |
| How does this change over time? | Revenue by month | Line chart |
| How does this break into parts? | Revenue by product, 2-4 slices | Stacked bar, or a small pie |
| How is this distributed? | Order sizes across all customers | Histogram |
| Is there a relationship between two things? | Ad spend vs. signups | Scatter plot |
| What's the single number right now? | This month's revenue vs. target | Card + KPI |

📝 **Terminology.** A visual gives you *the real picture* when equal differences in the data produce equal differences in what your eye perceives. A bar twice as tall should represent a value twice as large. When that link breaks - through a truncated axis, a mismatched scale, or a 3D tilt - the chart is technically built from real numbers but visually asserting something false.

💡 **Key point.** Power BI has one bug you can't file a ticket for: it will render *any* chart type against *any* field, correct or not. Nothing stops you from putting a continuous measure into a pie chart or plotting two wildly different scales on one axis. The tool trusts you to pick the right shape. This phase is that judgment.

## Where Power BI's own defaults betray you

These four are the ones that bite real reports, because each one looks *reasonable* the moment you build it.

**1. The truncated axis.** By default, Power BI's column and bar charts start the Y-axis at zero - good. But the moment you or a stakeholder switches on **Format visual → Y-axis → Range → Start** and types a non-zero value "to make the difference more visible," you've built a lie. A 2% change between two bars that start at 0 looks like 2%. The same two bars with an axis starting at 95 look far larger than 2% - one bar towering over its neighbor.

```text
   Axis starts at 0                    Axis starts at 95
   ┌──────────────┐                    ┌──────────────┐
   │         ▓▓    │                    │              │
   │         ▓▓    │                    │         ▓▓    │
   │  ▓▓     ▓▓    │                    │  ▓▓     ▓▓    │  ← same two values,
   │  ▓▓     ▓▓    │                    │  ▓▓─────▓▓    │     wildly different
   │  ▓▓     ▓▓    │                    └──────────────┘     story
   └──────────────┘
   Region A: 97    Region B: 99         Region A: 97    Region B: 99
```

⚠️ **The fix isn't "never truncate."** For a line chart tracking something that only ever moves in a narrow, meaningful band (a stock's intraday price, a server's CPU hovering near 100%), zooming the axis can be the clear choice - it's what lets you see real movement at all. The rule is narrower: if you truncate, say so. Label the axis start, or add a note. The lie isn't the truncation, it's the silence about it.

**2. Dual-axis combo charts.** Power BI's line-and-clustered-column chart lets you plot two measures on two independently-scaled axes - say, Revenue on the left and Units Sold on the right. This is one of the most requested visuals and one of the easiest to misuse: because the two axes scale independently, you can drag either one until the two lines "cross" dramatically, implying a relationship that's really just two arbitrary scales lining up. If you genuinely need two measures on one chart, keep both axes starting at zero and label which line belongs to which axis clearly - don't let Power BI auto-scale them into a coincidence.

**3. 3D and decorative effects.** Power BI's native visuals don't ship 3D pie charts, but the custom visual gallery still has plenty, and people import them because they look impressive in a screenshot. A 3D pie tilts slices so the ones in "front" look larger than equal slices in "back," purely from the camera angle - the data didn't change, your perception of it did. Same problem with drop shadows and heavy gradients on bars: they add visual weight that isn't in the number. If a formatting choice can't be explained by "this represents the data," it's decoration, and decoration in a chart is a small tax on the reader's ability to read it correctly.

**4. Pie charts past four or five slices.** A pie chart works because humans are decent at comparing a *few* angles, especially against a clean quarter or half. Past four or five slices, most people can no longer rank the wedges by eye - "Product C" and "Product F" might be off by 3 percentage points and you'd never know from the picture. Power BI will happily render fifteen slices in decreasingly-distinguishable shades of blue. When you have more than four or five categories, a sorted bar chart is almost always the clearer answer: it turns "guess the angle" into "read down a sorted list," which is a task humans are actually good at.

## Color that carries meaning, not decoration

Power BI's default theme assigns colors to categories in the order they appear, which is fine until the *same* category (say, "Region: West") gets a different color on two different pages because the underlying field order changed. Set up a **theme** (Format → Themes, or a custom JSON theme) once, so a category's color is consistent everywhere it appears. That consistency is what lets a reader learn "orange means West" once and reuse it across your whole report instead of re-learning the legend on every page.

Use color changes to mean something specific, not to make a chart prettier:

- **Conditional formatting** (data bars, color scales, icon sets on a table) should map directly to a threshold that matters - red below target, green above - not to an arbitrary gradient.
- Reserve your most saturated, attention-grabbing color for the one series that matters most (this year vs. last year, actual vs. target). Everything else can sit in muted gray so the eye knows where to land first.
- Never use color as the *only* signal for something important - a portion of any audience is colorblind, and red/green "good/bad" without a label or icon is invisible to them.

## Context lives in the card and the tooltip, not just the chart

Phase 6 already established that a bare number is a trivia question - "$84,000" means nothing without a comparison, a target, and a trend. In Power BI that context is built with specific, small features:

```dax
Revenue vs LY % =
DIVIDE(
    [Total Revenue] - [Total Revenue LY],
    [Total Revenue LY]
)
```

Drop that measure next to `[Total Revenue]` on a **KPI** visual and the reader gets "up 12% vs. last year" at a glance instead of having to hold last year's number in their head. Add a **target** in the KPI visual's format pane (or a target measure via [Time Intelligence](07-time-intelligence.md) patterns) and Power BI colors the indicator green or red against it, so the card answers "good or bad?" as well as "which direction?" That's the same comparison-target-trend triad from [Bi Dashboards That Work](/guides/bi-dashboards-that-work), just built with Power BI's specific card and KPI controls instead of generic dashboard tiles.

Tooltips deserve the same discipline. Power BI lets you build a **report-page tooltip** - a small custom visual that appears on hover, showing a mini trend line or a breakdown. Use it to hold the *detail* a chart doesn't have room for, instead of cramming a dozen data labels onto the main visual and making it noisy for everyone who didn't need that detail.

## Recap

1. **Name the question's shape first** - comparison, trend, part-to-whole, distribution, relationship, single value - and let that pick the chart family, not visual appeal.
2. **A truncated axis, a mismatched dual-axis scale, 3D tilt, and a pie past four or five slices** are Power BI's four most common ways to make a correct number look like the wrong thing. Each has a specific, clearer alternative.
3. **Color should carry meaning** - a consistent theme per category, saturation reserved for what matters, and never color alone as the only signal.
4. **Context (comparison, target, trend) belongs on the card and in the tooltip**, built with measures like a year-over-year % and a target line, not left for the reader to remember.

Getting the individual visual right is half the job. The other half is arranging several of them on one page so the most important answer hits the eye first - that's next.

## Quick check

Test yourself on the idea that demystifies this phase - that a chart can be built from correct numbers and still visually assert something false:

```quiz
[
  {
    "q": "A stakeholder asks you to set the Y-axis to start at 90 instead of 0 so a small revenue bump is 'easier to see.' What's the actual problem with doing that silently?",
    "choices": [
      "It makes equal differences in the data produce unequal differences on screen, so a 2% change can visually read as a far bigger gap",
      "Power BI will refuse to render the chart with a non-zero axis start",
      "It's fine as long as the underlying DAX measure is still correct",
      "Nothing - axis start is purely a formatting choice with no effect on interpretation"
    ],
    "answer": 0,
    "explain": "The numbers stay correct, but truncating the axis breaks the link between the size of a visual difference and the size of the real one - which is the definition of a chart that shows the real picture."
  },
  {
    "q": "You have a report field with 12 product categories and want to show revenue share for each. Why is a pie chart the wrong default here, even though the data is correct?",
    "choices": [
      "Past four or five slices, people can no longer rank the wedges by eye, so a sorted bar chart communicates the same data far more clearly",
      "Pie charts can only display percentages that sum to under 50%",
      "Power BI limits pie charts to 8 slices and will drop the rest",
      "Pie charts are never appropriate for any part-to-whole question"
    ],
    "answer": 0,
    "explain": "Pie charts work for a handful of slices because humans are decent at comparing a few angles; past four or five, a sorted bar chart turns 'guess the angle' into 'read down a sorted list.'"
  },
  {
    "q": "A dual-axis combo chart shows Revenue (left axis) and Units Sold (right axis) crossing dramatically in the middle of the year. What should you check before trusting that 'crossing point' means anything?",
    "choices": [
      "Whether the two axes are independently auto-scaled, since two arbitrary scales can be nudged until any two lines appear to cross",
      "Whether the chart uses a line or a column for each measure",
      "Whether both measures are formatted as currency",
      "Nothing - a visual crossing point always indicates a real relationship in the data"
    ],
    "answer": 0,
    "explain": "Independently-scaled axes can make unrelated series 'cross' anywhere; the crossing point only means something if both axes start at zero and the scales are chosen deliberately, not auto-fit."
  }
]
```


---

# Building Reports & Dashboards

You've got a clean model (Phase 4) and measures that answer real questions (Phase 5, Phase 6). Now comes the part most people picture when they hear "Power BI" at all: a canvas full of charts someone can actually click around in. This phase is about that canvas - and about a word collision that trips up nearly everyone starting out, because Power BI uses "report" and "dashboard" for two genuinely different things.

## Report vs. dashboard: not the same word twice

**What they actually are.** A **report** is a multi-page canvas you build in Power BI Desktop (or in the browser). It's made of visuals wired to your data model, and it's fully interactive - click a bar, and every other visual on the page reacts. A **dashboard** is a single page, built only in the Power BI *Service* (the web app, after you publish), made of **tiles** you pin from one or more reports. A dashboard tile is closer to a snapshot than a chart: click it and it takes you back to the live report behind it, but the tile itself doesn't cross-filter anything else on the dashboard.

**Why the split exists.** They solve different problems. A report is for *exploring* - slicing sales by region, drilling into a weird spike, comparing this quarter to last. A dashboard is for *monitoring* - one screen, pulled from several reports if needed, that answers "is everything still fine?" at a glance, first thing in the morning, without clicking anything. Once you see them as "explore" vs. "check," the two-tool split stops being confusing and starts being useful: you build one report with real depth, then pin the three or four numbers someone actually needs to see daily onto a dashboard.

💡 **Key point.** Everything in this phase up to the last section happens in a *report*. The dashboard is what you get at the very end, by pinning pieces of that report.

## The canvas and its filters

A report page is a grid of visuals, each bound to fields and measures from your model. What makes it more than a static picture of charts is the **filter pane**, which works in layers:

| Filter level | Scope | Typical use |
|---|---|---|
| Visual-level | One visual only | "Just this chart, top 10 products" |
| Page-level | Every visual on this page | "This whole page is Q3 only" |
| Report-level | Every page in the report | "Exclude test accounts everywhere" |

Filters stack from the top down - a visual sees the report filter, then the page filter, then its own filter, narrowing each time. Set the broad rule once at the report level instead of repeating it on every visual; that's the same instinct as pushing shaping decisions upstream in Power Query (Phase 3) rather than patching them in ten places downstream.

## Cross-filtering: the interactivity that's on by default

Click a bar in one visual, and every *other* visual on the page filters itself to match, instantly, with no code. This is **cross-filtering**, and it's on by default because it comes free from the relationships you built in Phase 4 - click "West" on a region bar chart, and the model follows that one-to-many relationship out to every fact row tagged West, updating every other visual to match.

```text
[Region bar chart]        [Sales by month]         [Top products]
  West  ████████            (click West bar,          (click West bar,
  East  █████                 this redraws to           this redraws to
  North ███                   West-only trend)          West-only ranking)
```
*What just happened:* one click, three visuals updated, zero code. This is the payoff for the modeling work in Phase 4 - a clean star schema is what makes cross-filtering *correct* instead of just fast. A visual can also be set to cross-*highlight* instead of filter (dim the rest instead of removing it), or excluded from the interaction entirely per pair of visuals, from Format > Edit interactions.

## Slicers: filters you can see and click

A **slicer** is a filter rendered as its own visual on the canvas - a list, a dropdown, a date range - so the viewer sets it themselves instead of you hard-coding a value. Drop a Date slicer and a Region slicer at the top of a page, and every visual below responds to whatever the viewer picks, the same cross-filtering mechanism as clicking a chart, just driven by a control built for exactly that job.

⚠️ **The gotcha.** A slicer only filters visuals on its *own page* by default. If you want one date range to hold across every page of the report, sync the slicer (View > Sync slicers) instead of copy-pasting it onto each page - copies drift out of sync the moment someone changes one and forgets the others.

## Bookmarks: saving a view, not just a picture

A **bookmark** captures the current state of a page - which filters are set, which visuals are visible, even scroll position - so you can jump back to it with one click. Combine two bookmarks with a pair of buttons and you get a toggle: a "This Year / Last Year" switch, or a "Summary / Detail" view swap, all on one page, with no extra pages built.

```text
[This Year] [Last Year]   <- two buttons, each linked to a bookmark
     ↑
  currently selected bookmark applies its saved filter state
  to the visuals below, instantly
```

Bookmarks are also how you build a guided walkthrough: a **bookmark navigator** groups several bookmarks into a slideshow-style sequence, useful for a report meant to be presented rather than freely explored.

## Drillthrough: click into the detail

A **drillthrough** page is a detail page a viewer reaches by right-clicking a data point and choosing "Drill through" - Power BI carries the clicked value (say, one product) along as an implicit filter and lands on a page built to show everything about *that one thing*. It's the report-building answer to "I don't want to cram every possible detail onto the summary page" - keep the overview page clean, and let anyone who needs the deep dive click through to it.

## Tooltips: a whole report page as a hover

By default, hovering a data point shows a small tooltip with its value. You can replace that with a **tooltip page**: a small report page (toggled on with "Allow use as tooltip" under Page information in its page settings) that renders inside the hover instead of a plain box, showing a mini chart or a few extra measures. It's the same drillthrough idea, but for a glance instead of a click.

## From report to dashboard: pinning

Once the report is published to the Service, open it there and pin any individual visual (or a whole page) to a dashboard - it becomes a tile. Pin the handful of numbers someone genuinely checks every morning, not the whole report; a dashboard that tries to be the report defeats the point of having two tools. One thing only a dashboard tile can do that a report visual can't: a **data alert** (notify you when a numeric tile crosses a threshold). Dashboards also carry a natural-language **Q&A** box, where a viewer can type "total sales last quarter by region" and get a chart back on the fly - though reports can host a Q&A visual for the same type-a-question interaction.

## Recap

1. **Report and dashboard are different tools.** Reports (Desktop, multi-page, interactive) are for exploring; dashboards (Service, single-page, pinned tiles) are for monitoring at a glance.
2. **Filters stack** visual → page → report; push a rule as high as it applies to avoid repeating it.
3. **Cross-filtering is automatic** because of the relationships from Phase 4 - click one visual, the rest follow.
4. **Slicers** put filtering in the viewer's hands; sync them across pages instead of duplicating them.
5. **Bookmarks** save a filter state for one-click toggles or guided walkthroughs; **drillthrough** and **tooltip pages** push detail out of the summary view until someone asks for it.
6. **Pin sparingly.** A dashboard is the two or three numbers someone checks every morning, not a copy of the report.

## Quick check

Test yourself on the ideas that trip people up most in this phase - the report/dashboard split and why cross-filtering needs no code:

```quiz
[
  {
    "q": "What's the actual difference between a Power BI report and a dashboard?",
    "choices": [
      "A dashboard is built in Desktop, a report is built in the Service",
      "A report is a multi-page interactive canvas built in Desktop; a dashboard is a single page of tiles pinned from one or more reports, built in the Service",
      "They're two names for the same canvas",
      "A dashboard is a static, printed export of a report"
    ],
    "answer": 1,
    "explain": "Reports are for exploring - multi-page, fully interactive, built in Desktop. Dashboards are for monitoring - one page, pinned tiles, built only in the Service after publishing."
  },
  {
    "q": "You click 'West' on a region bar chart and two other visuals on the page redraw instantly. Why, with no code written?",
    "choices": [
      "Power BI reruns a background script on every click",
      "Cross-filtering follows the relationships already built into the data model, so the click propagates through the star schema automatically",
      "The visuals were manually synced together in Format settings",
      "Dashboards refresh their tiles live, and this page is a dashboard"
    ],
    "answer": 1,
    "explain": "Cross-filtering is on by default because it rides the relationships from the model (Phase 4) - a clean star schema is what makes it correct, not some extra wiring on the visuals."
  },
  {
    "q": "A slicer on page 1 has no effect on page 2. What's the right fix?",
    "choices": [
      "Copy-paste the slicer onto page 2",
      "Sync the slicer across pages (View > Sync slicers)",
      "Rebuild the relationship in the model",
      "Switch the slicer to a page-level filter"
    ],
    "answer": 1,
    "explain": "A slicer only filters its own page by default. Syncing keeps one control driving every page; copies drift out of sync the moment someone changes one and forgets the others."
  }
]
```


---

# Publishing & Sharing (Workspaces, Apps, RLS)

Everything so far has lived on your laptop, in Power BI Desktop, visible to exactly one person: you. This phase is where that changes. You'll take the `.pbix` file you've been building and turn it into something that lives on the web, that a manager can open from their phone, and that shows the sales director every region's numbers while showing a regional manager only their own region - from the *same* report, with no copies and no manual filtering.

Three ideas do all the work here: **workspaces** (where reports live once they leave your laptop), **apps** (how you hand a finished report to an audience without handing them a construction site), and **row-level security** (how one report shows different rows to different people). Get the mental model for each and the buttons are almost incidental.

## The mental model: workspace, app, audience

**A workspace is a shared folder with muscle.** In the Power BI Service (app.powerbi.com), a workspace holds the published reports, the semantic models (the data models behind them - Microsoft renamed these from "datasets" in late 2023, so older tutorials still call them that), and a list of people who can edit them - like a shared drive, except the "files" are live, queryable data models, not static documents. Everyone added to a workspace as a member or admin can open the raw report, poke at the model, and publish new versions. That's exactly right for the three or four people building the report together. It's exactly wrong for the two hundred people who just want to look at the numbers - giving all two hundred edit access to the workspace means any of them could accidentally rename a table or delete a page.

That's the problem an **app** solves. An app is a read-only, curated view of some (not necessarily all) of a workspace's reports, published separately and shared with an audience that never sees the workspace at all. Think of the workspace as the kitchen and the app as the dining room: the cooks need access to every drawer and the stove, the diners need a clean plate. You keep building and iterating in the workspace; when a version is ready, you publish it out as an app, and that's what the two hundred people actually open.

```
Power BI Desktop (.pbix, your laptop)
        │  Publish
        ▼
   Workspace  ──────────────►  built by 2-4 editors, changes constantly
        │  Publish app
        ▼
      App     ──────────────►  opened by 200 viewers, changes on your schedule
```

The gap between those two arrows matters: publishing a new app version is a deliberate step, not automatic. You can rework a broken visual in the workspace all afternoon without a single viewer seeing the mess, then publish the app once when it's actually done.

## Publishing from Desktop to a workspace

From Power BI Desktop, with your file saved: **Home ribbon → Publish**, pick a workspace (or create one), and Desktop uploads both the report and the semantic model behind it. The first time you do this you'll be prompted to sign in - this is the moment mentioned back in phase 1, where a Microsoft 365 / organizational account finally becomes necessary, because a workspace is a cloud object tied to your organization's tenant.

Two workspace types worth knowing apart:

- **My Workspace** - your personal sandbox, visible only to you. Fine for testing a publish, wrong for anything a second person needs to see.
- **A (proper) workspace** - created with a name, given members with roles (Viewer, Contributor, Member, Admin), and this is where real, shared work happens.

Once published, open the report in a browser at app.powerbi.com. It's the same report, live-querying the same in-memory model you built in phases 4-7 - just reachable from anywhere instead of from one laptop.

## Three ways to share, and when each is right

Power BI gives you three genuinely different sharing mechanisms, and picking the wrong one is the most common publishing mistake:

| Method | Who can do what | Best for |
|---|---|---|
| **Direct share** ("Share" button on a report) | One specific person gets viewer (or edit) access to that one report | A single colleague, a quick one-off |
| **Workspace access** | Members and admins get full editor access to every report/semantic model; viewers get read-only | The build team, 2-5 people, ongoing collaboration |
| **App** | Everyone in the audience gets read-only access to the curated set of reports you published | A department, a leadership team, anyone who should never see the underlying model |

If you find yourself adding twenty names to "Share" one at a time, that's a sign you want an app instead - it's built for exactly that fan-out, and it lets you update the audience list, the reports included, and the navigation in one place rather than twenty individual shares.

## Row-level security: one report, different rows per viewer

Here's the problem RLS solves. Say your Sales report has a Region column, and you want the East regional manager to see only East's numbers, West's manager to see only West's, and the VP to see everything - without maintaining three separate reports that all drift out of sync the moment someone edits a measure.

**What RLS actually is:** a filter, written once as a DAX expression, that Power BI silently applies to every query a given user runs against the model - on top of whatever slicers and filters are already on the page. The user never sees the filter; they just see fewer rows, as if that's all that ever existed.

You build it in Desktop under **Modeling → Manage Roles**. A role is a name plus a DAX filter expression applied to a table:

```dax
[Region] = "East"
```

That one line, saved as a role called `East Manager` and applied to the `Sales` table, means: anyone assigned this role only ever sees `Sales` rows where `Region` equals `"East"` - and because of how the relationships you built in phase 4 propagate filters, anything summarized from `Sales` (totals, charts, cards) inherits that restriction automatically. You don't have to repeat the filter on every visual.

Before publishing, test it in Desktop with **View as Roles** - pick the role, and the whole report re-renders as that user would see it. This step is not optional. RLS bugs are invisible to you (the report author, who normally sees everything) and glaringly obvious to the one person who suddenly can't see their own region - catch it here, not in a Monday morning email.

### Static vs. dynamic RLS

The `[Region] = "East"` role above is **static**: one role per region, and you manually assign users to roles after publishing (Workspace → semantic model → Security). That's fine for five regions. It falls over at fifty, because you're hand-maintaining fifty role-to-people mappings forever.

**Dynamic RLS** fixes this by looking up the viewer's own identity instead of hard-coding a value:

```dax
[Region] = LOOKUPVALUE(
    UserRegionMap[Region],
    UserRegionMap[Email], USERPRINCIPALNAME()
)
```

`USERPRINCIPALNAME()` returns the email of whoever is currently viewing the report. `LOOKUPVALUE` finds that email in a small mapping table (`UserRegionMap`, one row per person, sitting in your model just for this purpose) and pulls back their assigned region. One role, no per-person assignment - add a row to the mapping table when someone joins the sales team, and RLS just works for them. This is the version worth reaching for the moment you have more than a handful of viewer groups.

## Recap

1. **Workspaces** hold reports for the small team actually building them; give editor access there only to people who should be able to change the model.
2. **Apps** are the read-only, curated distribution layer - publish one when a workspace is ready for a real audience, and update it on your own schedule.
3. Pick sharing by audience size and access level: direct share for one person, workspace access for co-builders, an app for everyone else.
4. **RLS** is a DAX filter (`Manage Roles` in Desktop) applied automatically to every query a user makes; always verify it with **View as Roles** before publishing.
5. **Static RLS** hard-codes a value per role and needs manual user assignment; **dynamic RLS** looks up the viewer via `USERPRINCIPALNAME()` against a mapping table and scales to any number of people with zero per-person setup.

### Check yourself

```quiz
[
  {
    "q": "You need to give 200 people in the sales department read-only access to a finished report, without exposing the underlying semantic model. What should you use?",
    "choices": [
      "Add all 200 people as workspace members",
      "Publish an app and share it with them",
      "Use the direct-share Share button 200 times",
      "Give them all the Contributor role in the workspace"
    ],
    "answer": 1,
    "explain": "Workspace access hands out editor rights to the model itself, which is wrong for a read-only audience of that size - an app is the read-only, curated distribution layer built for exactly this fan-out."
  },
  {
    "q": "You wrote a role with the filter [Region] = \"East\" and assigned it to a user, but forgot to test it before publishing. What's the real risk?",
    "choices": [
      "None - RLS filters are validated automatically by the Power BI Service before publish",
      "The report will fail to publish until the role is tested",
      "The bug is invisible to you as the author (you see everything) and only shows up when that user opens the report and sees the wrong or missing rows",
      "The filter only applies to new rows added after publishing"
    ],
    "answer": 2,
    "explain": "As the report author you normally see all rows, so a broken RLS filter looks completely fine to you - View as Roles exists specifically to catch that mismatch before a real viewer does."
  },
  {
    "q": "A company has 50 regional managers, each needing to see only their own region. Why would you reach for dynamic RLS instead of static RLS?",
    "choices": [
      "Dynamic RLS runs faster because it skips the filter for small tables",
      "Static RLS can only support up to 10 roles, so 50 regions is impossible without dynamic RLS",
      "Dynamic RLS looks up each viewer's region from a mapping table via USERPRINCIPALNAME(), so adding a new manager means adding a row instead of hand-building and assigning a 51st role",
      "Dynamic RLS doesn't require testing with View as Roles"
    ],
    "answer": 2,
    "explain": "Static RLS needs one hard-coded role per region plus a manual user-to-role assignment for each person; dynamic RLS replaces all of that with a single role that looks up the current viewer's region from a mapping table, so it scales to any number of people with no per-person setup."
  }
]
```


---

# Refresh, Gateways & Capacity Basics

Go back to phase 1 for a second: a Power BI report is a snapshot of a database, built by replaying a fixed set of Power Query steps against a source. That snapshot is frozen the moment you hit publish. Nobody looking at the report in the Service is running live queries against your source system (with a couple of exceptions we'll get to) - they're looking at whatever was in the model the last time it refreshed. This phase is about that last part: how "the last time it refreshed" actually happens, why it sometimes can't, and the limits you'll hit as a report grows from "thing I built for my team" into "thing three hundred people rely on every morning."

## The mental model: refresh is a replay, not a sync

When Power BI refreshes a dataset, it does not creep through your source data looking for what changed. It reopens the `.pbix` (or the published dataset's equivalent), reruns every Power Query step from phase 3 top to bottom against the live source, and rebuilds the VertiPaq model from scratch. Full replay, every time, by default.

That single fact explains almost everything confusing about refresh:

- **Why it's slow on huge tables.** A 50-million-row fact table gets re-pulled and re-compressed in full on every refresh, unless you've told Power BI otherwise.
- **Why a broken Power Query step breaks refresh entirely, not partially.** The replay either runs to completion or fails - there's no "load what worked, skip what didn't."
- **Why refresh needs a live path back to the source.** Something has to actually run those queries against SQL Server, the SharePoint list, the API - and that something has to run *on a schedule, without you sitting at your laptop*.

That last point is the whole reason gateways exist, so let's follow it through.

## Scheduled refresh: who presses the button when you're asleep

In Power BI Desktop, refresh is you, clicking Refresh, watching a progress bar. Once a report is published to the Service, nobody wants to be the person who opens Desktop every morning at 6am to keep the dashboard current. **Scheduled refresh** is the Service doing that click for you, automatically, on a timer.

You configure it on the dataset's settings page in the Service: pick times of day (up to 8 refreshes/day on Pro, up to 48/day on Premium capacities), and Power BI runs the same replay Desktop would have run - reconnect to every source in Get Data, rerun every Power Query step, rebuild the model.

The part people trip over: **scheduled refresh only works if the Service can reach every source unattended.** A cloud source like a public API or an Azure SQL database with a stored username and password is easy - the Service just calls it directly. A source that only exists *inside your company's network* (an on-premises SQL Server, a network file share, a local Excel file on someone's laptop) is not reachable from Microsoft's cloud at all. That gap is what a gateway closes.

## The gateway: a bridge, not a magic trick

**What it actually is.** The **On-premises Data Gateway** is a small Windows service you install on a machine that sits inside your network *and* can reach the internet - often a spare server, sometimes someone's always-on desktop. It holds an encrypted, outbound-only connection to the Power BI Service. When a scheduled refresh needs data from an on-prem SQL Server, the Service doesn't reach into your network - it asks the gateway, the gateway runs the query locally where it already has access, and sends the result back out.

**Why this exists.** Companies do not open inbound firewall ports for a cloud service to poke into their internal database. That would be a serious security hole for the entire company just so one report can refresh. The gateway flips the direction: all traffic is outbound from your network to Microsoft, initiated by a service you control, using credentials you registered with it - never an inbound connection initiated by the cloud.

**Two modes worth knowing apart:**

| | **Personal mode gateway** | **Standard (enterprise) mode gateway** |
|---|---|---|
| Who uses it | One person, one machine | Whole team/org, shared |
| Sources | Only that person's own reports | Any registered dataset that needs it |
| High availability | No - if that laptop is off, refresh fails | Yes - install on 2+ machines as a cluster |
| Typical use | "I have one Excel file on my desktop I refresh from" | "Our SQL Server needs to feed a dozen production reports" |

If you're the only person who'll ever touch this, personal mode is genuinely fine and takes ten minutes to set up. The moment a report matters to other people, move to standard mode on a machine that isn't going to get closed at 5pm.

**A refresh failure you'll actually hit:** "Failed to update data source credentials" or a timeout with no useful detail almost always traces back to one of three things - the gateway machine was off, the stored source credentials expired (a password rotated, an OAuth token lapsed), or a Power Query step references a local file path that only exists on your laptop, not the gateway machine. Check those three before you assume DAX or the model is at fault; refresh failures are usually plumbing, not logic.

## Why some sources skip the replay entirely: DirectQuery and dataflows

Two things bend the "full replay every time" rule, and it's worth knowing they exist even at from-zero level:

- **DirectQuery mode** (an alternative to the Import mode you've used all guide) doesn't store a copy of the data at all - every visual sends a live query straight to the source when someone opens the report. No refresh needed, no VertiPaq cache, but every click is only as fast as the source database, and DAX has real restrictions in this mode. It's a deliberate trade of speed for freshness, not a free upgrade.
- **Dataflows** let you run the Power Query transformation step *once*, centrally, in the Service, and have multiple datasets reuse that cleaned output instead of each one re-pulling and re-cleaning the same source table. Worth knowing the name exists; treat it as a "when a team outgrows copy-pasting Power Query steps between reports" tool, not a phase-11 requirement.

## Incremental refresh: stop re-pulling history that never changes

Full replay is wasteful for a very common shape of data: a fact table of dated transactions where last year's rows never change, only this month's do. **Incremental refresh** tells Power BI to partition the table by date and only reprocess the recent partitions - say, refresh the last 5 days in full, and leave the previous 3 years alone, untouched, every single run.

You set this up with two parameters, `RangeStart` and `RangeEnd`, applied as a filter on your date column in Power Query, then a policy in the dataset settings ("refresh rows from the last N days, keep M years of history"). The payoff is real: a refresh that took 40 minutes because it re-pulled 3 years of transactions can drop to 2 minutes once it's only re-pulling this week. Incremental refresh - including the scheduled, unattended runs in the Service - works on a plain Pro license; you do not need Premium for the basic partition-and-refresh-recent behavior. What Premium adds on top is the real-time DirectQuery "hybrid" partition and advanced partition management through the XMLA endpoint. Either way, treat it as the tool you reach for once a fact table gets large and slow, not day-one setup.

## Capacity: what Pro buys you vs. what Premium buys you

This is the part where "just publish it" quietly runs into a wall, so it's worth naming the limits plainly rather than letting you discover them the hard way:

| | **Pro (per user)** | **Premium (per capacity / per user)** |
|---|---|---|
| Dataset size | 1 GB per dataset | 100 GB+ per dataset |
| Scheduled refreshes | Up to 8/day | Up to 48/day |
| Sharing | Only with other Pro-licensed users | Anyone in the org, even without a license (capacity-based) |
| Incremental refresh | Yes - configure and run on a schedule | Yes - plus real-time DirectQuery partition |
| Dedicated hardware | No - shared infrastructure | Yes - your own reserved compute |

The practical read: Pro is where every report in this guide has lived, and it's genuinely enough for small teams and datasets under a gigabyte. You hit Premium's door when one of three things happens - your dataset crosses ~1GB (common with a few years of daily transactional detail), you need to share with people who don't have individual Pro licenses, or refresh performance becomes the bottleneck and even incremental refresh can't keep a run inside Pro's tighter refresh window and shared compute. None of that is a decision to make in phase 11 - it's a decision to make when your monitoring (which you set up in phase 10 with RLS and workspace roles) tells you refreshes are creeping past your window or a dataset is inching toward the 1GB ceiling.

## Recap

1. **Refresh is a full replay** of every Power Query step against the live source, not an incremental sync - that's why a broken step fails the whole thing and why huge tables refresh slowly by default.
2. **Scheduled refresh** is the Service running that replay on a timer so nobody has to click Refresh by hand; it needs an unattended, live path to every source.
3. **The gateway** bridges that gap for on-premises sources - an outbound-only, encrypted connection from a machine inside your network, in personal mode (one person) or standard mode (shared, can be clustered for high availability).
4. **Incremental refresh** partitions a fact table by date so only recent rows reprocess, turning a 40-minute refresh into minutes - it runs on Pro too, scheduled and unattended; Premium only adds the real-time DirectQuery partition and advanced XMLA partition control.
5. **Pro tops out around 1GB datasets and 8 refreshes/day**; Premium buys bigger datasets, more refreshes, dedicated capacity, and license-free sharing - reach for it when the numbers actually demand it, not before.

## Check yourself

Test yourself on the ideas that trip people up most - what refresh actually does, and which direction the gateway connection runs:

```quiz
[
  {
    "q": "A scheduled refresh runs on a dataset. What does Power BI actually do?",
    "choices": [
      "It scans the source for new or changed rows and appends only those",
      "It reruns every Power Query step from scratch against the live source and rebuilds the whole model",
      "It re-downloads the last published .pbix file",
      "It refreshes only the visuals that changed since the last refresh"
    ],
    "answer": 1,
    "explain": "Refresh is a full replay, not a sync - that's why one broken Power Query step fails the whole refresh instead of just skipping the bad part."
  },
  {
    "q": "Why doesn't the Power BI Service just connect directly into your company's network to query an on-premises SQL Server?",
    "choices": [
      "It does - the gateway just speeds that direct connection up",
      "Because that would require an inbound firewall port into your network, which companies don't open for a cloud service; the gateway instead makes an outbound-only connection and runs the query locally",
      "Because on-premises data can't be queried by any automated process, only by a human",
      "Because the gateway first copies the whole database into the cloud, so no live connection is ever needed"
    ],
    "answer": 1,
    "explain": "The gateway flips the direction: it's an outbound connection initiated from inside your network to Microsoft, so nobody has to open an inbound port for the cloud to reach in."
  },
  {
    "q": "You've set up incremental refresh with RangeStart/RangeEnd on a Pro-licensed dataset and want it to run automatically every night. What happens?",
    "choices": [
      "It runs on the schedule - incremental refresh, including scheduled unattended runs, is supported on Power BI Pro",
      "It fails - scheduled incremental refresh requires Premium or Premium-per-user",
      "Incremental refresh isn't available on Pro at all, even to configure",
      "It runs, but reprocesses all history every time instead of just the recent partitions"
    ],
    "answer": 0,
    "explain": "Incremental refresh is supported on Pro, Premium, PPU, and Embedded, and a Pro dataset can run it on a schedule. What Premium adds is the real-time DirectQuery hybrid partition and advanced XMLA partition management - not the scheduling itself."
  }
]
```