3.4 — Calculation Groups, Dynamic Format Strings and Field Parameters
DP-600 Domain 3 · Skill 3.4
What You Need to Know
These three features make models smaller and more flexible. Instead of writing 30 measures (10 base measures × YTD, PY, YoY), you write the logic once and let users switch.
Calculation groups
- A calculation group is a special table whose calculation items change any measure that's in context. Each item's expression uses
SELECTEDMEASURE().
-- Calculation group "Time Intelligence", column "Period"
Current = SELECTEDMEASURE()
YTD = CALCULATE ( SELECTEDMEASURE(), DATESYTD ( 'Date'[Date] ) )
PY = CALCULATE ( SELECTEDMEASURE(), SAMEPERIODLASTYEAR ( 'Date'[Date] ) )
YoY % = VAR cur = SELECTEDMEASURE()
VAR py = CALCULATE ( SELECTEDMEASURE(), SAMEPERIODLASTYEAR ( 'Date'[Date] ) )
RETURN DIVIDE ( cur - py, py )
- Put the calculation group's column on a matrix's columns or in a slicer, and every measure gets YTD, PY and YoY % automatically.
- You can create them in Power BI Desktop (Model view → Calculation group) or in Tabular Editor.
- Creating one sets the model to discourage implicit measures, so users must use explicit measures. Dragging a raw column no longer sums it.
- Precedence decides the order when several calculation groups apply. Use
SELECTEDMEASURENAME()orISSELECTEDMEASURE()to treat some measures differently. - A calculation item can have its own format string expression, for example
"0.0%"for YoY %, while the other items keep the measure's format.
Dynamic format strings for measures
- Set a measure's format to Dynamic and write a DAX expression that returns a format string:
-- Show millions as "1.2M" and thousands as "850K"
SWITCH ( TRUE (),
SELECTEDMEASURE() >= 1e6, "#,0.0,,""M""",
SELECTEDMEASURE() >= 1e3, "#,0,""K""",
"#,0" )
-- In DAX strings a quote is escaped by doubling it ("")
- The value stays numeric, so it still sorts, charts and aggregates. That's the advantage over
FORMAT(), which returns text. - Typical uses: currency symbols chosen by a currency slicer, and scaling to K or M.
Field parameters
- Modeling → New parameter → Fields creates a calculated table (built with
NAMEOF) listing measures and/or columns. The slicer it creates lets users choose what a visual shows. - Example: one bar chart where the user picks the axis (Category, Region or Channel) or the value (Revenue, Units or Margin).
SELECTEDVALUEon the parameter's column tells measures and titles which field was picked, for example for a dynamic title.
Which one when?
| Requirement | Feature |
|---|---|
| Apply the same time-intelligence variants to many measures without duplicating them | Calculation group |
| A measure's display format must change with the value or a selection, while staying numeric | Dynamic format string |
| Report users choose which measure or dimension a visual shows | Field parameter |
Key Concept: Calculation groups transform any measure through SELECTEDMEASURE(). Dynamic format strings change display only and keep numbers numeric. Field parameters let users swap fields in a visual.
Exam Traps
- Writing YTD, PY and YoY copies of every measure instead of one calculation group
- Using
FORMAT()inside a measure for display and then being unable to sort or chart it - Expecting users to drag columns for implicit sums after a calculation group has been added
- Building one visual per field instead of using a field parameter
Check your understanding
Practice Scenario
🔒 This practice scenario is part of Skillmarked Pro
The lessons stay free. Pro unlocks the other 17 DP-600 practice scenarios and the 50-question timed mock exam, plus everything for every other exam.
Launch Deal: first month €9.95, then €19.90/month. Enter code LAUNCH50 at checkout. Ends 1 December.
Cancel anytime. Or €199 a year, which saves 17%.
Not sure yet? Try 10 free mock exam questions.
Already Pro? Log in.
Sources
- Calculation groups, Microsoft Learn
- Create dynamic format strings for measures, Microsoft Learn
- Let report readers use field parameters to change visuals, Microsoft Learn
- SELECTEDMEASURE function, Microsoft Learn