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

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.

[6]

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.

[63][237]

dax
-- Item: Current
SELECTEDMEASURE ()
-- Item: PY
CALCULATE ( 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 )
Group: Time Intelligence. Column: Time Calculation. Three calculation items.

[63][237]

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.

MeasureCurrentPYYoY %
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").

[63][237]

[61][63][237]

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.

[237]

Where you build a calculation group

Exam depth: recognize it.

WhereHow
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.

[63][237]

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.

[63][237]

[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.

[237]

Nine PY measures, or one PY calculation item that fits every measure. Picture: Vintage instruction-manual drawing, two numbered panels in faded blue ink on cream paper.

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.

[237][238]

dax
-- Format expression on the measure [Supplier Cost]
-- EUR picked: 1234.5 shows as €1,234.50. Nothing or several picked: $1,234.50
SELECTEDVALUE ( 'Currency'[Format], "$#,0.00" )
-- Format expression on a calculation item: fall back to the measure's own format
SELECTEDVALUE ( 'Currency'[Format], SELECTEDMEASUREFORMATSTRING () )
Juniper pays suppliers in several currencies. A Currency table has a Format column.

[237][238]

[237]

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.

  1. On the Modeling tab, select New parameter > Fields.
  2. 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.

[239]

dax
KPI = {
( "Revenue", NAMEOF ( 'Sales'[Revenue] ), 0 ),
( "Units", NAMEOF ( 'Sales'[Units] ), 1 ),
( "Margin %", NAMEOF ( 'Sales'[Margin %] ), 2 )
}
The DAX Power BI writes for a KPI switcher. Each row: display name, field, order.

[239]

[239]

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.

[239]

Calculation group, field parameter or separate measures?

Exam depth: know it cold.

RequirementUse
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.

[237][238][239]

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.

  1. Open Model view and select Calculation group on the ribbon.
  2. If asked to turn on Discourage implicit measures, select Yes.
  3. The first item holds SELECTEDMEASURE(). Rename it Current.
  4. Rename the group Time Intelligence and its column Time Calculation.
  5. Right-click Calculation items and select New calculation item. Name it PY and enter its expression.
  6. Add YoY %. In Properties, turn on Dynamic format string and enter "0.0%".
  7. In Report view, build a matrix: Month on Rows, Time Calculation on Columns, Revenue in Values.

[63][237]

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.

[63][238]

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.

[63][237]

FlashcardsIndex cardsFlashcards ⚡

Card 1 of 15

[63][237][238][239]

Worth keeping

  1. Calculation group: write PY or YoY % once with SELECTEDMEASURE(); it fits every explicit measure.
  2. Turn on Discourage implicit measures; implicit measures ignore calculation items.
  3. Highest precedence wraps outermost, and its format string wins.
  4. Dynamic format strings change the look but keep numbers numeric; FORMAT makes text.
  5. Field parameter: the reader picks which measure or column a visual shows.

[237]

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.

  1. [6]Microsoft Learn: Study guide for Exam DP-600: Implementing Analytics Solutions Using Microsoft Fabric
  2. [61]Microsoft Learn: DAX glossary
  3. [63]Microsoft Learn: Create calculation groups
  4. [230]Microsoft Learn: OFFSET
  5. [231]Microsoft Learn: INDEX
  6. [233]Microsoft Learn: SELECTEDVALUE
  7. [237]Microsoft Learn: Calculation groups
  8. [238]Microsoft Learn: Create dynamic format strings for measures
  9. [239]Microsoft Learn: Use field parameters to let report readers change visuals