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)
22 min
Relationships: star schemas, bridge tables and many-to-many
Build relationships with the right cardinality and filter direction, including bridge tables and many-to-many.
By the end of this lesson you can
- Build a star schema in a semantic model with one-to-many relationships
- Set cardinality, cross-filter direction and active or inactive relationships
- Model many-to-many with a bridge table or a many-to-many relationship
- Spot the traps: bidirectional filters, RLS paths and blank rows
From earlier lessons · 1 of 2
A Juniper Coffee model keeps Sales in DirectQuery and an aggregated table in Import. The Store table filters both. Which storage mode should the Store table use?
Pick one answer.
From earlier lessons · 2 of 2
In a star schema, which kind of table holds the numeric columns you add up and the keys that point to other tables?
Pick one answer.
Juniper Coffee's marketing lead opens the loyalty report. Maya has 400 points, Leo has 300, and the total says 400. She thinks the report is broken. It isn't: Maya and Leo share a household loyalty account. One relationship setting decides whether this report is right, wrong, or slow.
A star schema in a semantic model
Exam depth: know it cold.
A relationship is a path that carries a filter from a column in one table to a column in another. In a star schema, every dimension (a table of things, such as Store or Product) sits on the "one" side. Every fact (a table of events, such as Sales) sits on the "many" side.
There's no setting that marks a table as a fact or a dimension. The relationships decide it: the "one" side is the dimension.
The usual build steps:
- Give each dimension one unique key column, such as
StoreKey. - Relate each dimension to the fact, one-to-many, single direction.
- Hide the key columns on the "many" side, so report authors group by the dimension instead.
- Mark your own date table as the date table (next section).
Mark as date table
Exam depth: know it cold.
In Power BI Desktop, right-click your date table and choose Mark as date table. Then pick its date column. Power BI checks that the column has unique values, no nulls, and every date from start to end with no gaps.
You must mark it when you use the classic time intelligence functions. You must also mark it when the relationship uses an integer key such as 20261231 instead of a Datetime column.
Marking it removes the automatic hidden date tables. Visuals built on those automatic tables stop working. Newer calendar-based time intelligence (preview) doesn't need the mark.
Cardinality and cross-filter direction
Exam depth: know it cold.
Cardinality says whether each side's column holds unique values. Cross-filter direction says which way filters flow: Single (one way) or Both (bidirectional).
| Cardinality | What it means | Direction options |
|---|---|---|
| One-to-many (or many-to-one) | One side unique, the other repeats. | Single or Both. |
| One-to-one | Both sides unique. Rare, often a design smell. | Both only. |
| Many-to-many | Both sides can repeat. | Single either way, or Both. |
With a "one" side, filters always flow from it. If a refresh loads a duplicate into a "one" side column, the whole refresh fails.
Decision rule: one-to-many, single direction, unless a named pattern below needs more.
Active, inactive and role-playing dimensions
Exam depth: know it cold.
Only one active relationship (a solid line) can exist between two tables. Any extra one must be inactive (a dashed line). An inactive relationship carries no filter until a measure turns it on with USERELATIONSHIP.
A role-playing dimension is one table that filters a fact in different roles. Juniper's MobileOrders table has OrderDate and PickupDate, both pointing at Date.
Two ways to model it:
- One Date table, one inactive relationship. Fine when no visual needs both roles at once.
- One table per role, such as Date and Pickup Date, each with an active relationship. Use it when reports filter both roles together.
Orders Picked Up =CALCULATE(COUNTROWS(MobileOrders),USERELATIONSHIP('Date'[Date], MobileOrders[PickupDate]))
You are an analytics engineer at Juniper Coffee. MobileOrders has OrderDate and PickupDate. Store managers want one matrix with order month on rows and pickup month on columns. What should you do?
Pick one answer.
Bridge tables for many-to-many dimensions
Exam depth: know it cold.
At Juniper, a customer can hold a personal loyalty account and share a household account. A household account has several customers. That's many-to-many between two dimensions.
You add a bridge table (Microsoft's docs say bridging table): one row per customer-account pair, IDs only. A table with keys and no measures is also called a factless fact table.
The pattern:
- Customer one-to-many to CustomerAccount (the bridge).
- Account one-to-many to CustomerAccount.
- Account one-to-many to PointsEarned (the fact).
- Set one relationship, Account to CustomerAccount, to Both, so a customer filter can reach the fact.
- Hide the bridge and its ID columns.
Predict, then reveal
The household account earned 300 points and Maya's personal account earned 100. Maya is on both; Leo is only on the household one. The visual shows Maya 400, Leo 300, total 400. Is the total a bug?
Many-to-many cardinality for higher-grain facts
Exam depth: know it cold.
A different case: finance budgets sit at region and month, not store and day. The Budget table has a Region column that repeats, and so does Store's Region. No side is unique.
- For the region: create a relationship with many-to-many cardinality from Store to Budget, single direction.
- For the month: store the first day of each month, such as 2026-11-01. Then relate Date to Budget one-to-many.
- Guard the measure: return BLANK when a lower level, such as a single store or day, is filtered. Otherwise each store shows the whole region's budget.
- Hide the Budget number column, so only the measure summarizes it.
| Need | Use |
|---|---|
| Two dimensions that relate many-to-many. | A bridge table plus one Both relationship. |
| A fact stored at a higher grain than the dimension. | A many-to-many cardinality relationship. |
| Two fact tables. | Shared dimensions, not a direct relationship. |
A many-to-many cardinality relationship is a limited relationship: the engine can't trust either side to be unique. So it joins at query time with inner-join logic, and RELATED can't use it.
Fastest to slowest filter paths:
- One-to-many relationships in the same source group (one store of data, such as all Import tables).
- Many-to-many through a bridge, with a Both relationship.
- Many-to-many cardinality relationships.
- Relationships across source groups, such as Import to DirectQuery.
Budget Amount =IF(NOT ISFILTERED(Store[StoreKey])&& NOT ISFILTERED(Store[StoreName])&& NOT ISFILTERED('Date'[Date]),SUM(Budget[BudgetAmount]))
You are an analytics engineer at Juniper Coffee. A promotion covers many products, and each product appears in many promotions. Marketing wants sales by promotion. Sales relates to Product. What should you add?
Pick one answer.
Why Both is risky
Exam depth: know it cold.
A Both relationship makes queries do more work, and it can confuse users when slicer options appear and vanish. Use it only where a pattern needs it: one-to-one, and the bridge table.
Both can also create two paths between the same tables. That's ambiguity. Power BI Desktop may refuse to save such a relationship and show an error. Fix it by making a relationship inactive, or by loading the table twice, once per role.
Cheaper alternatives:
| You want | Instead of Both |
|---|---|
| A slicer that lists only products with sales. | A visual-level filter on the slicer: the measure is not blank. |
| A dimension-to-dimension count, such as regions per product. | CROSSFILTER(..., BOTH) inside that one measure. |
RLS filters use single direction by default, even across a Both relationship. To let an RLS filter flow both ways, open the relationship and select Apply security filter in both directions. A table in several Both relationships can have this on for only one of them. It can slow queries, so test it.
You are a BI developer at Juniper Coffee. The Region slicer should list only regions that have sales for the selected product. The model is a clean star. You need the least query overhead. What should you do?
Pick one answer.
Try it in Power BI Desktop
Power BI Desktop is free. Load two tables, such as Store and Sales, then:
- On the Modeling tab, select Manage relationships > New.
- Pick Sales and its
StoreKeycolumn, then Store and itsStoreKey. - Check Cardinality (Many to one) and Cross filter direction (Single). Select OK.
- In Model view, see the line:
*and1show cardinality, the arrow shows direction. - Add a second relationship between the same two tables. It shows as a dashed line: inactive.
- Double-click a line to open the relationship editor and change its settings.
Exam-style practice
You are an analytics engineer at Juniper Coffee. Finance loads monthly budgets by region. Each month is stored as its first day. A store-level matrix shows every store with the full regional budget. What should you do?
Pick one answer.
You are a BI developer at Juniper Coffee. Store managers see only their region through an RLS role on Store. The Customer table relates to Sales. Managers must see only customers who bought in their region. What should you configure?
Pick one answer.
Worth keeping
- Default: one-to-many from dimension to fact, single direction, many-side keys hidden.
- One active path per table pair; inactive paths need USERELATIONSHIP and never carry RLS.
- Two dimensions many-to-many: bridge table plus one Both relationship; totals are non-additive.
- Higher-grain facts: many-to-many cardinality, single direction, and a measure that blanks lower levels.
- Use Both only where a pattern needs it; slicer filters and CROSSFILTER are cheaper.
On the job
A typical week: marketing asks why loyalty totals don't add up, and finance asks why every store shows the regional budget. Both answers are relationships.
You open Model view and trace each filter from the slicer to the fact, arrow by arrow. Dashed lines, double arrows and many-to-many marks are where the surprises live. You fix the model, not each report, because ten reports share it.
Then you write a one-line table description in the model, such as "Customer totals count shared accounts once". Report authors see it as a tooltip, and the support questions stop.
Your relationships now carry filters exactly where you want them. But who decides which filters a measure sees, and how does CALCULATE change them on the fly? That's filter context, next.
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 (10)
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
- [33]Microsoft Learn: Understand star schema and the importance for Power BI
- [60]Microsoft Learn: Model relationships in Power BI Desktop
- [130]Microsoft Learn: Create and manage relationships in Power BI Desktop
- [200]Microsoft Learn: Table storage mode in Power BI semantic models
- [205]Microsoft Learn: Many-to-many relationship guidance
- [206]Microsoft Learn: Bi-directional relationship guidance
- [207]Microsoft Learn: Active vs inactive relationship guidance
- [208]Microsoft Learn: Set and use date tables in Power BI Desktop
- [209]Microsoft Learn: Row-level security (RLS) with Power BI