Black Friday50% off your first month on any monthly plan with EARLY50 See plans
All 35 lessons
  1. Start here: cloud, data, Fabric and Power BI basics

    1. 1. Start here: the exam, the plan and what you already know
    2. 2. Cloud in plain words: SaaS, PaaS, IaaS and where Fabric fits
    3. 3. Data basics: files, tables, formats and the lakehouse idea
    4. 4. A tour of Microsoft Fabric: OneLake, workspaces and items
    5. 5. Power BI in plain words: semantic models, reports and DAX
  2. Prepare data: get data in (exam area 2, 45-50%)

    1. 6. Connect to data and find it: connections, gateways, OneLake catalog and Real-Time hub
    2. 7. Choosing a data store: lakehouse, warehouse or eventhouse
    3. 8. Use data without copying: shortcuts, mirroring and OneLake integration
    4. 9. Ingest data: Dataflow Gen2, pipelines, Copy job and notebooks
    5. 10. Power Query in Dataflow Gen2: filter, data types, merge and new columns
    6. 11. Messy data: duplicates, missing values and nulls
  3. Prepare data: transform and query (exam area 2)

    1. 12. Views, functions and stored procedures in T-SQL
    2. 13. Build a star schema in a lakehouse or warehouse
    3. 14. Aggregate, join and enrich: SQL and PySpark side by side
    4. 15. Query with the Visual query editor and SQL
    5. 16. Query with KQL
    6. 17. Query with DAX: DAX query view and EVALUATE
  4. Design and build semantic models (exam area 3, 25-30%)

    1. 18. Choose a storage mode: Import, DirectQuery, Direct Lake and Dual
    2. 19. Relationships: star schemas, bridge tables and many-to-many
    3. 20. DAX foundations: measures, filter context, CALCULATE and variables
    4. 21. DAX functions the exam tests: iterators, table filters, windows and information functions
    5. 22. Calculation groups, dynamic format strings and field parameters
    6. 23. Composite models and large semantic model storage format
  5. Optimize models, then secure them (exam areas 3 and 1)

    1. 24. Direct Lake in depth: fallback, refresh and the two flavors
    2. 25. Incremental refresh for semantic models
    3. 26. Faster reports and DAX: finding and fixing slow queries
    4. 27. Who can see what: workspace roles and item permissions
    5. 28. Fine-grained security: rows, columns, objects and files
    6. 29. Governance: sensitivity labels and endorsement
  6. Lifecycle, then exam practice (exam area 1)

    1. 30. Version control: Git integration and Power BI projects (.pbip)
    2. 31. Deployment pipelines and impact analysis
    3. 32. XMLA endpoint and reusable assets: .pbit, .pbids and shared semantic models
    4. 33. Mock exam 1
    5. 34. Final review and exam day: every decision on one page
    6. 35. Mock exam 2
See the leaderboard

18 min

Power BI in plain words: semantic models, reports and DAX

See what a semantic model is, how Power BI Desktop and the service work together, and where DAX fits.

By the end of this lesson you can

  • Say what a semantic model holds that a report doesn't
  • Tell a measure, a calculated column and a calculated table apart
  • Split the work between Power BI Desktop, the service and Power Query
  • Predict what a simple measure shows in each cell of a matrix

From earlier lessons · 1 of 2

Juniper Coffee keeps every Lakehouse and Warehouse table in one logical store that all Fabric engines read. What is that store called?

Pick one answer.

From earlier lessons · 2 of 2

In which table format does Fabric store Lakehouse and Warehouse tables in OneLake?

Pick one answer.

Juniper Coffee's finance team opens three reports on Monday. Each one shows "January sales" for the North region. Each one shows a different number. Every report author wrote their own formula, in their own file, on their own copy of the data. Your manager asks for one number everyone can trust.

[6]

What a semantic model is

Exam depth: know it cold.

A semantic model is a ready-for-reporting layer over your data: tables plus the business logic that makes them mean something. Its old name was dataset, so older pages and questions may use that word.

A semantic model holds:

  • Tables, usually a star schema: dimension tables you filter and group by, and fact tables you summarize.
  • Relationships that link the tables, so a filter on one table flows to another.
  • Measures, the named calculations such as Total Sales.
  • Hierarchies, such as Region > Store, so visuals can drill down.
  • Security, such as row-level security (RLS) roles that limit which rows each person sees.

A report is a set of pages with visuals. It holds no business logic of its own. It sends queries to one semantic model and draws the answers. So one model can feed many reports, and every report shows the same numbers.

[33][49][50][53][60]

Rendering diagram…
The logic lives once, in the model. Reports only draw what the model answers.

[50][53]

The tiny model this course reuses

Exam depth: know it cold.

Lessons 17 and 20 calculate on this exact model, so get to know it. It has two tables.

Store (a dimension table, one row per café):

StoreRegion
Harbour.North
Old Mill.North
Riverside.South

Sales (a fact table, one row per order):

OrderIDDateStoreProductQtyAmount
1.2026-01-10HarbourLatte28
2.2026-01-15HarbourMuffin13
3.2026-01-20Old MillLatte312
4.2026-02-05RiversideLatte14
5.2026-02-12RiversideMuffin412
6.2026-02-20Old MillMuffin26

A one-to-many relationship links Store[Store] to Sales[Store]. One store has many orders. When a visual filters Store to the North region, the relationship passes that filter to Sales. Only orders 1, 2, 3 and 6 remain.

[33][60]

Power BI Desktop and the service

Exam depth: know it cold.

Power BI Desktop is a free Windows app for building models and reports. The Power BI service is the website (app.powerbi.com) where content is shared, refreshed and secured.

TaskUsual place
Shape data with Power Query.Desktop, or a Dataflow Gen2 in Fabric.
Build tables, relationships, measures.Desktop, or web modeling in the service.
Design report pages.Desktop or the service.
Share, set refresh, manage access.The service.

Publishing sends a Desktop file to a workspace. You get two items: a semantic model and a report. Edits made to that report in the service are not saved back to your .pbix file.

Other authors can then live connect to the published model from Desktop. They build their own reports and never copy the data.

In Fabric you can also start a model from a Lakehouse or Warehouse with New semantic model. That model uses Direct Lake, which lesson 18 explains.

[51][52][53][59]

Exam depth: recognize it.

Web modeling means editing a semantic model in the browser. Select the model's More options (...), then Open semantic model. Some Fabric pages call the same action Open data model.

  • The model opens in Viewing mode. Switch to Editing mode to change it.
  • You can add measures, calculated columns and tables, relationships and RLS roles. Changes save automatically.
  • You need Write permission on the model. An admin can turn the feature off for the tenant.

[54][59]

[50]

You are an analytics engineer at Juniper Coffee. You published a sales model from Power BI Desktop. Three report authors each need their own report on it. Their numbers must match yours, and no one may copy the data. What should they do?

Pick one answer.

[53]

Power Query shapes, DAX calculates

Exam depth: know it cold.

Two languages do two different jobs.

  • Power Query is a data preparation engine with a point-and-click editor. It cleans and shapes data before it loads: remove columns, fix data types, filter rows, merge tables. Each step is written in the M language behind the scenes. It runs at refresh time.
  • DAX (Data Analysis Expressions) is the formula language of the semantic model. It calculates on data that's already loaded: totals, ratios, year-to-date. It also writes RLS rules.
Power Query (M)DAX
When it runs.At data refresh.Mostly at query time.
Typical job.Clean, shape, combine.Calculate business numbers.
Reacts to slicers.No.Measures do.

Rule: fix the data in Power Query (or upstream in the Lakehouse). Calculate the business numbers in DAX.

[55][56][57][61]

The three DAX objects

Exam depth: know it cold.

You add DAX to a model as one of three objects.

MeasureCalculated columnCalculated table
What it is.A named formula that returns one value.A new column, one value per row.A whole new table.
Computed.On demand, for each cell of a visual.At refresh, then stored.At refresh, then stored.
Reacts to slicers.Yes.No.No.
Where you use it.Values in a visual.Rows, columns, slicers, filters.Like any table.
Model size.Adds nothing.Grows the model.Grows the model.

Rule: if the number must change with filters, write a measure. If you need a value to slice or group by, write a calculated column. Create both in Desktop with New measure and New column.

A custom column in Power Query looks like a calculated column. It's written in M and built before the data loads.

[55][58]

dax
// Measure: one value, recalculated for every cell of a visual
Total Sales = SUM ( Sales[Amount] )
// Calculated column on Sales: one stored value per row
Size = IF ( Sales[Qty] >= 3, "Large", "Small" )
// Rows 1-6 get: Small, Small, Large, Small, Large, Small
// Calculated table: a stored copy of the Latte rows (orders 1, 3, 4)
Latte Orders = FILTER ( Sales, Sales[Product] = "Latte" )
One of each DAX object, on the Juniper Sales table.

[55]

Predict, then reveal

You build a matrix with Store[Region] on rows and the Total Sales measure in values. Then you swap the rows for the Size column. What does each cell show?

[55][58]

Explicit and implicit measures

Exam depth: recognize it.

A measure you write in DAX, like Total Sales, is an explicit measure. The Data pane marks it with a calculator icon.

Now drag the Sales[Amount] column straight into a visual. Power BI sums it for you without any formula. That automatic Sum, Average or Count is an implicit measure. Columns that allow it show a sigma (∑) icon.

Explicit measureImplicit measure
Made by.You, with New measure and DAX.The visual, from a dragged column.
Logic.Any DAX, written once in the model.One basic aggregation.
Calculation groups (lesson 22).Work with it.Ignore it.
Analyze in Excel (MDX queries).Works.Can't use it.

The model property Discourage implicit measures blocks new implicit measures. Columns lose the ∑ icon and can't be added as values. Visuals that already use one keep working. Power BI asks you to turn it on when you add a calculation group.

Rule: official numbers are explicit measures.

[33][61][62][63]

Filter context in one sentence

Exam depth: recognize it.

Filter context is the set of filters in force when a measure is calculated for one cell. In the North row above, the filter is Region = North. The relationship passes it to Sales, so SUM only sees four orders and returns 29.

Rows, columns, slicers and report filters all add to filter context. DAX can change it too. Lesson 20 teaches this in full, because almost every DAX exam question turns on it.

[57][60][61]

[55][57]

Match

Match each Juniper need to the right tool.

Pick an item on the left, then its match on the right.

[55][56]

Three reports, three formulas, three answers. One semantic model, one answer. Picture: Vintage instruction-manual drawing, beige paper and thin black ink lines.

You are an analytics engineer at Juniper Coffee. Store managers use a Region slicer on a report page. They need average order value, and it must change when they pick a region. What should you add to the model?

Pick one answer.

[55]

Your job: a model people trust

Exam depth: recognize it.

An analytics engineer turns prepared tables into a trusted semantic model that others build reports on. At Juniper Coffee that means:

  1. Shape and load the Lakehouse tables into a star schema.
  2. Relate them and hide the key columns report authors don't need.
  3. Write the official measures once: Total Sales, Orders, Sales YTD.
  4. Add RLS so each store manager sees only their region.
  5. Publish and share the model, and let report authors live connect.

Report authors then build pages without writing core formulas. When finance asks why two reports disagree, the answer is that they can't.

[33][49][53]

Exam-style practice

You are an analytics engineer at Juniper Coffee. The supplier price list arrives with store names in mixed case and stray spaces. Relationships fail because names don't match. You need the fix applied every refresh, before data loads. What should you use?

Pick one answer.

[55][56]

You are a BI developer at Juniper Coffee. Finance and store managers use two different reports. Both must show the same Total Sales, and managers must see only their region. Where should the formula and the region rule live?

Pick one answer.

[49][50]

FlashcardsIndex cardsFlashcards ⚡

Card 1 of 15

[50][52][53][54][55][56][57][62]

Worth keeping

  1. The semantic model holds the logic: tables, relationships, measures, hierarchies and security. Reports only draw it.
  2. One trusted model, many live-connected reports, one set of numbers.
  3. Power Query shapes data before load; DAX calculates on the loaded model.
  4. Must react to filters: measure. Must slice or group: calculated column.

You now know a model needs clean tables underneath it. But how do those tables reach Fabric from Juniper's SQL database, an on-premises file share and a stream of loyalty events? Next lesson: connections, gateways and finding data.

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 (17)

Each fact in the lesson is marked with the number of the page it comes from.

  1. [6]Microsoft Learn: Study guide for Exam DP-600: Implementing Analytics Solutions Using Microsoft Fabric
  2. [33]Microsoft Learn: Understand star schema and the importance for Power BI
  3. [49]Microsoft Learn: Semantic models in the Power BI service
  4. [50]Microsoft Learn: Power BI semantic models in Microsoft Fabric
  5. [51]Microsoft Learn: What is Power BI?
  6. [52]Microsoft Learn: Publish semantic models and reports from Power BI Desktop
  7. [53]Microsoft Learn: Connect to semantic models in the Power BI service from Power BI Desktop
  8. [54]Microsoft Learn: Edit semantic models in the Power BI service
  9. [55]Microsoft Learn: Use calculation options in Power BI Desktop
  10. [56]Microsoft Learn: What is Power Query?
  11. [57]Microsoft Learn: DAX overview
  12. [58]Microsoft Learn: Create measures for data analysis in Power BI Desktop
  13. [59]Microsoft Learn: Create a Power BI semantic model
  14. [60]Microsoft Learn: Model relationships in Power BI Desktop
  15. [61]Microsoft Learn: DAX glossary
  16. [62]Microsoft Learn: Work with Model explorer
  17. [63]Microsoft Learn: Create calculation groups