All 35 lessons
Start here: cloud, data, Fabric and Power BI basics
- 1. Start here: the exam, the plan and what you already know
- 2. Cloud in plain words: SaaS, PaaS, IaaS and where Fabric fits
- 3. Data basics: files, tables, formats and the lakehouse idea
- 4. A tour of Microsoft Fabric: OneLake, workspaces and items
- 5. Power BI in plain words: semantic models, reports and DAX
Prepare data: get data in (exam area 2, 45-50%)
- 6. Connect to data and find it: connections, gateways, OneLake catalog and Real-Time hub
- 7. Choosing a data store: lakehouse, warehouse or eventhouse
- 8. Use data without copying: shortcuts, mirroring and OneLake integration
- 9. Ingest data: Dataflow Gen2, pipelines, Copy job and notebooks
- 10. Power Query in Dataflow Gen2: filter, data types, merge and new columns
- 11. Messy data: duplicates, missing values and nulls
Prepare data: transform and query (exam area 2)
Design and build semantic models (exam area 3, 25-30%)
- 18. Choose a storage mode: Import, DirectQuery, Direct Lake and Dual
- 19. Relationships: star schemas, bridge tables and many-to-many
- 20. DAX foundations: measures, filter context, CALCULATE and variables
- 21. DAX functions the exam tests: iterators, table filters, windows and information functions
- 22. Calculation groups, dynamic format strings and field parameters
- 23. Composite models and large semantic model storage format
Optimize models, then secure them (exam areas 3 and 1)
- 24. Direct Lake in depth: fallback, refresh and the two flavors
- 25. Incremental refresh for semantic models
- 26. Faster reports and DAX: finding and fixing slow queries
- 27. Who can see what: workspace roles and item permissions
- 28. Fine-grained security: rows, columns, objects and files
- 29. Governance: sensitivity labels and endorsement
Lifecycle, then exam practice (exam area 1)
18 min
Incremental refresh for semantic models
Set up incremental refresh with RangeStart and RangeEnd so big models refresh only new data.
By the end of this lesson you can
- Explain why incremental refresh makes big Import tables refresh faster
- Set up RangeStart, RangeEnd and the filter with the right >= and < pattern
- Choose the policy settings: archive, refresh, detect data changes, complete days, real time
- Know what happens after publishing, and why Direct Lake doesn't use it
From earlier lessons · 1 of 3
A Juniper Coffee Direct Lake model is refreshed after a nightly lakehouse load. What does that refresh do?
Pick one answer.
From earlier lessons · 2 of 3
A Juniper Coffee Import model on a Fabric capacity will grow past 10 GB after refresh. Which setting must be on?
Pick one answer.
From earlier lessons · 3 of 3
Customers share loyalty accounts through a CustomerAccount bridge table. How many relationships in that pattern should filter in both directions?
Pick one answer.
Juniper Coffee's Sales table holds three years of till lines, 900 million rows. Every night the model reloads all of them, and the refresh now runs past opening time. Yet only the last few days ever change. Why reload 2024 every night?
What incremental refresh does
Exam depth: know it cold.
Incremental refresh splits a big Import table into partitions (slices of rows by date). Each refresh reloads only the recent partitions and leaves old ones alone.
You set it up as a refresh policy: rules such as "keep 3 years, refresh the last 7 days". You define it in Power BI Desktop, and the service applies it.
The gains: faster, more reliable refreshes, less load on the source, and room for models with billions of rows. Old partitions merge as periods close, and the oldest drop off. That's a rolling window.
It works in Pro, Premium Per User, Premium and Embedded workspaces.
RangeStart, RangeEnd and the filter
Exam depth: know it cold.
In Power Query Editor you create two parameters with reserved, case-sensitive names: RangeStart and RangeEnd. Both must be the Date/Time type.
Then you filter the table's date column with them. In Desktop, their values load just a small recent slice, so the file stays light. After publishing, the service replaces the values for each partition it builds.
If you define policies on several tables, they all use the same two parameters. If the date column is an integer key such as 20261119, write a small function that turns the Date/Time parameter into that number.
letSource = Sql.Database("juniper-pos.database.windows.net", "pos"),Sales = Source{[Schema = "dbo", Item = "SalesLine"]}[Data],Filtered = Table.SelectRows(Sales,each [SaleDateTime] >= RangeStart and [SaleDateTime] < RangeEnd)inFiltered
Predict, then reveal
A teammate writes each [SaleDateTime] > RangeStart and [SaleDateTime] < RangeEnd. The totals come out slightly low after publishing. What's wrong?
Query folding is the requirement
Exam depth: know it cold.
Query folding means Power Query turns your steps into one query the source runs, such as a SQL WHERE clause. For incremental refresh, the RangeStart and RangeEnd filter must fold.
If it doesn't fold, an Import-only policy still works, but badly. The engine pulls every row from the source and filters locally, so refresh is as slow as before. With real-time data (below), non-folding steps aren't allowed at all.
SQL sources such as Azure SQL Database fold reliably. If Desktop can't confirm folding, the policy dialog shows a warning. Keep the filter step right after the source step.
You are an analytics engineer at Juniper Coffee. You set an incremental refresh policy on Sales, but each refresh takes as long as a full load. The policy dialog warned that it couldn't confirm query folding. What's the most likely cause?
Pick one answer.
The two required policy settings
Exam depth: know it cold.
In Desktop, right-click the table and select Incremental refresh. Turn on Incrementally refresh this table. The switch stays greyed out until the query filters on RangeStart and RangeEnd.
| Setting | What it decides | Juniper example |
|---|---|---|
| Archive data starting ... before refresh date | How much history the model keeps. | 3 years. |
| Incrementally refresh data starting ... before refresh date | Which recent rows reload on every refresh. | 7 days. |
Pick the shortest refresh period that still catches late changes. Juniper's tills can void a sale up to a week later, so 7 days.
The optional settings
Exam depth: know it cold.
| Setting. | What it does. | Pick it when. |
|---|---|---|
| Detect data changes. | Reloads a refresh period only if the max of a last-updated column changed. | The source has an audit column, such as ModifiedAt. |
| Only refresh complete days. | Skips today's partial day. | Finance wants whole days only. |
| Get the latest data in real time with DirectQuery (Premium only). | Adds a DirectQuery partition for rows after the refresh period. | Reports need today's rows within minutes. |
The change column must not be the column you partition on. Detect data changes doesn't see deleted rows, only soft deletes (rows flagged as deleted, with a new timestamp).
Refresh runs in UTC unless you set a time zone in the model's refresh settings. That decides when "today" starts.
You are an analytics engineer at Juniper Coffee. Till corrections can change sales up to 30 days back, but most days don't change. SalesLine has SaleDate and ModifiedAt columns. You need the fewest partitions reloaded. What should you configure?
Pick one answer.
Hybrid tables: real time on top
Exam depth: know it cold.
A hybrid table is a table with Import partitions for history plus one DirectQuery partition for the newest rows. You get it by selecting Get the latest data in real time with DirectQuery.
What changes when you turn it on:
- Only refresh complete days is switched on automatically.
- Every step in the query must fold. No local workarounds.
- It needs a Premium, Premium Per User or Embedded workspace. A Pro workspace can't host it.
- Tables related to the hybrid table should use Dual storage mode, to avoid slow queries.
To show new rows without a click, pair it with automatic page refresh in the report.
After you publish
Exam depth: know it cold.
The published table starts with one partition. The first refresh in the service applies the policy: it creates the partitions and loads all the history. Run it manually so you can watch it; it's slow.
Later refreshes reload only the refresh partitions, so they're fast.
If the model may pass 1 GB, turn on Large semantic model storage format before that first refresh.
You can't see partitions in Desktop or the service. On a Premium-type capacity, connect through the XMLA endpoint (a connection that external tools use). In SQL Server Management Studio you can then view partitions and refresh one old partition alone. Refreshes through the XMLA endpoint have no time limit. Service refreshes stop at two hours in Pro and five hours in Premium.
Direct Lake doesn't use incremental refresh
Exam depth: recognize it.
Incremental refresh is for Import tables (and hybrid tables built on them). Direct Lake tables don't support model table partitions or hybrid tables.
They don't need them: a Direct Lake refresh is framing, which copies only metadata and takes seconds. If a big lakehouse table needs slicing, partition the Delta table itself.
Decision rule: a slow Import refresh of a big, date-stamped table means incremental refresh. A Direct Lake model never does.
You are an analytics engineer at Juniper Coffee. The Sales model uses Direct Lake on a Fabric lakehouse. A colleague wants an incremental refresh policy to make refresh faster. What should you tell them?
Pick one answer.
Try it in Power BI Desktop
Use any table with a Date/Time column, such as an Azure SQL sample.
- Select Transform data. In Power Query Editor, select Manage Parameters > New Parameter.
- Name it
RangeStart, type Date/Time, and give it a recent value. Repeat forRangeEnd, a day or two later. - On the date column, select Date Filters > Custom Filter. Choose is after or equal to > Parameter > RangeStart, then is before > Parameter > RangeEnd. If your version doesn't offer Parameter, type the
Table.SelectRowsstep in the formula bar instead. - Select Close & Apply. Only the small window loads.
- In Table view, right-click the table and select Incremental refresh.
- Turn on the switch, set the archive and refresh periods, and select Apply.
Put in order
Put the incremental refresh setup in order
Drag, or focus an item and press Space, move with the arrow keys, then Space to drop.
- Define the policy: archive period and refresh period
- Publish the model to the workspace
- Run the first refresh, which creates the partitions
- Filter the date column with >= RangeStart and < RangeEnd
- Close & Apply to load the small window into Desktop
- Create the RangeStart and RangeEnd Date/Time parameters
Exam-style practice
You are an analytics engineer at Juniper Coffee. Sales comes from Azure SQL Database into an Import table, in a workspace on a Premium capacity. Store managers need today's sales within minutes. Three years of history must stay fast. You must not add data stores. What should you do?
Pick one answer.
You are a BI developer at Juniper Coffee. The Sales model in the service holds three years of partitions. You added one measure in Power BI Desktop. You must deploy it without reloading history. What should you do?
Pick one answer.
Worth keeping
- Incremental refresh partitions an Import table by date and reloads only recent partitions.
- Filter with >= RangeStart and < RangeEnd, and make sure the filter folds to the source.
- Archive decides history kept; refresh decides what reloads; detect data changes trims further.
- Real time adds a DirectQuery partition: a hybrid table, Premium-type workspaces only.
- Never republish a partitioned model from Desktop; Direct Lake doesn't use any of this.
Refresh is fast now. But store managers say one report page still takes eight seconds to draw. Is it the model, the DAX, or the visual? Next you find out, with the tools that time every query.
Leaderboard
Loading…
50 XP for each lesson you finish, 10 for each right answer. Names show only for learners who turn on Show me on leaderboards.
Sources for this lesson (8)
Each fact in the lesson is marked with the number of the page it comes from.
- [6]Microsoft Learn: Study guide for Exam DP-600: Implementing Analytics Solutions Using Microsoft Fabric
- [45]Microsoft Learn: Direct Lake overview
- [198]Microsoft Learn: How Direct Lake works
- [204]Microsoft Learn: Large semantic models in Power BI Premium
- [205]Microsoft Learn: Many-to-many relationship guidance
- [245]Microsoft Learn: Configure incremental refresh and real-time data for Power BI semantic models
- [250]Microsoft Learn: Configure incremental refresh and real-time data
- [251]Microsoft Learn: Advanced incremental refresh and real-time data with the XMLA endpoint