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)
20 min
Calculation groups, dynamic format strings and field parameters
Cut measure sprawl with calculation groups, format measures dynamically, and let users swap fields.
By the end of this lesson you can
- Build a calculation group with SELECTEDMEASURE and predict results when groups stack
- Add dynamic format strings to a measure and to a calculation item
- Create a field parameter so report readers can swap measures or columns
- Choose a calculation group, a field parameter or separate measures for a scenario
From earlier lessons · 1 of 2
Juniper Coffee wants every month's revenue compared with January of the same year. Which window function returns January's row on every month?
Pick one answer.
From earlier lessons · 2 of 2
A card must show the one region a slicer picked, or "All regions" when zero or several are picked. Which expression does that?
Pick one answer.
Juniper Coffee's finance team loves the new report. Now they want Current, Last year and YoY % for Revenue, Units and Margin. That's nine near-identical measures. Store managers also want one chart that switches between those KPIs. You can write it all by hand, or build it once.
Calculation groups: write the pattern once
Exam depth: know it cold.
A calculation group is a special table that holds reusable DAX patterns. Each pattern is a calculation item, such as PY or YoY %. Inside an item, SELECTEDMEASURE() is a placeholder for whatever measure the visual is showing.
In a report, the group looks like a table with one column. Put that column on a matrix's columns, or in a slicer. Each item then applies to every measure in the visual. One PY item replaces a PY version of every measure.
The problem it solves is measure sprawl: dozens of measures that differ only in their time logic.
-- Item: CurrentSELECTEDMEASURE ()-- Item: PYCALCULATE ( SELECTEDMEASURE (), SAMEPERIODLASTYEAR ( 'Date'[Date] ) )-- Item: YoY % (format string expression: "0.0%")VAR Cur = SELECTEDMEASURE ()VAR Prev = CALCULATE ( SELECTEDMEASURE (), SAMEPERIODLASTYEAR ( 'Date'[Date] ) )RETURN DIVIDE ( Cur - Prev, Prev )
Trace March 2026. Revenue is $120k, and March 2025 was $100k. Units are 2,700, down from 3,000. A matrix has Time Calculation on columns and both measures on values.
| Measure | Current | PY | YoY % |
|---|---|---|---|
| Revenue. | $120k. | $100k. | 20.0%. |
| Units. | 2,700. | 3,000. | -10.0%. |
An item applies only when exactly one item of that group is in the filter context. You can also call one item from a measure: CALCULATE([Revenue], 'Time Intelligence'[Time Calculation] = "PY").
You are an analytics engineer at Juniper Coffee. Finance needs MTD, YTD and PY versions of 15 existing measures. New measures will need them too. You need the least ongoing maintenance. What should you create?
Pick one answer.
Where you build a calculation group
Exam depth: recognize it.
| Where | How |
|---|---|
| Power BI Desktop, Model view. | Calculation group button on the ribbon. |
| Semantic model edited in the browser. | Same button, same steps. |
| TMDL view in Power BI Desktop. | A createOrReplace script with calculationGroup. |
| External tools such as Tabular Editor. | Connect through the XMLA endpoint (lesson 32). |
Items show alphabetically in reports unless you set their order. In Model view you reorder them in the Properties pane. Behind the scenes, an Ordinal column holds the sort number.
Precedence: when two groups stack
Exam depth: know it cold.
Precedence is a number on each calculation group. It sets the order groups wrap a measure when both apply.
Start with the highest-precedence item. Replace its SELECTEDMEASURE() with the next-highest item, and so on, down to the measure. So the highest precedence ends up outermost.
Precedence also decides formatting: only the highest-precedence group's dynamic format string is used.
An item may filter on another item of its own group, as PY YTD can call YTD. That's sideways recursion, and it's the only recursion allowed.
[Cups] returns 10. Group A (precedence 100) has item SELECTEDMEASURE() + 2. Group B (precedence 200) has item SELECTEDMEASURE() * 2. A report selects both items. What does [Cups] show?
Pick one answer.
Dynamic format strings: change the look, keep the number
Exam depth: know it cold.
A dynamic format string is a DAX expression that returns the format to use, such as "0.0%" or "€#,0.00". The value stays numeric, so charts still work. The FORMAT function, by contrast, turns the value into text.
On a measure: select the measure. On Measure tools, in Formatting, set Format to Dynamic. A drop-down left of the formula bar now switches between the measure's DAX and its Format expression.
On a calculation item: select the item and turn on Dynamic format string in the Properties pane. YoY % uses this to show a percentage whatever the measure.
Report measures in a live connect report can't have one.
-- Format expression on the measure [Supplier Cost]-- EUR picked: 1234.5 shows as €1,234.50. Nothing or several picked: $1,234.50SELECTEDVALUE ( 'Currency'[Format], "$#,0.00" )-- Format expression on a calculation item: fall back to the measure's own formatSELECTEDVALUE ( 'Currency'[Format], SELECTEDMEASUREFORMATSTRING () )
Field parameters: let readers swap the field
Exam depth: know it cold.
A field parameter lets report readers choose which measure or column a visual uses, from a slicer.
- On the Modeling tab, select New parameter > Fields.
- Name it and pick the fields. You can mix measures and columns, drag to reorder, and double-click to rename.
Power BI adds a small table to the model, defined in DAX. Put a column parameter on an axis, and a measure parameter in Values. The slicer lists the field names by default.
KPI = {( "Revenue", NAMEOF ( 'Sales'[Revenue] ), 0 ),( "Units", NAMEOF ( 'Sales'[Units] ), 1 ),( "Margin %", NAMEOF ( 'Sales'[Margin %] ), 2 )}
You are a BI developer at Juniper Coffee. Store managers want one bar chart and a slicer to switch it between Revenue, Units and Margin %. They can't edit reports. What should you create?
Pick one answer.
Calculation group, field parameter or separate measures?
Exam depth: know it cold.
| Requirement | Use |
|---|---|
| The same logic (PY, YTD, YoY %) on many measures. | Calculation group. |
| Readers choose which measure or column a visual shows. | Field parameter. |
| Only the display format changes with context. | Dynamic format string. |
| A few calculations, each with its own logic. | Separate measures. |
Decision rule: if the logic repeats, use a calculation group. If the reader picks the field, use a field parameter. If only the look changes, use a dynamic format string. They combine well: a field parameter of measures, sliced by a time-intelligence group.
Try it in Fabric
Use Power BI Desktop, or edit a semantic model in the Fabric portal. You need a Date table and a [Revenue] measure.
- Open Model view and select Calculation group on the ribbon.
- If asked to turn on Discourage implicit measures, select Yes.
- The first item holds
SELECTEDMEASURE(). Rename it Current. - Rename the group Time Intelligence and its column Time Calculation.
- Right-click Calculation items and select New calculation item. Name it PY and enter its expression.
- Add YoY %. In Properties, turn on Dynamic format string and enter
"0.0%". - In Report view, build a matrix: Month on Rows, Time Calculation on Columns, Revenue in Values.
Exam-style practice
You are an analytics engineer at Juniper Coffee. The YoY % calculation item shows 0.20 for Revenue and -0.10 for Units. It must show 20.0% and -10.0%, and line charts must still plot it. What should you do?
Pick one answer.
You are an analytics engineer at Juniper Coffee. An old visual shows "Sum of Amount", made by dragging the Sales[Amount] column in. The Time Calculation slicer changes every other visual, but not this one. What should you do?
Pick one answer.
Worth keeping
- Calculation group: write PY or YoY % once with SELECTEDMEASURE(); it fits every explicit measure.
- Turn on Discourage implicit measures; implicit measures ignore calculation items.
- Highest precedence wraps outermost, and its format string wins.
- Dynamic format strings change the look but keep numbers numeric; FORMAT makes text.
- Field parameter: the reader picks which measure or column a visual shows.
Finance now wants five years of till lines in the model, plus live sales from today. One storage mode can't do both. Next lesson: composite models and the large semantic model storage format.
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 (9)
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
- [61]Microsoft Learn: DAX glossary
- [63]Microsoft Learn: Create calculation groups
- [230]Microsoft Learn: OFFSET
- [231]Microsoft Learn: INDEX
- [233]Microsoft Learn: SELECTEDVALUE
- [237]Microsoft Learn: Calculation groups
- [238]Microsoft Learn: Create dynamic format strings for measures
- [239]Microsoft Learn: Use field parameters to let report readers change visuals