Launch Deal · 50% off 1st month ·

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() or ISSELECTEDMEASURE() 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).
  • SELECTEDVALUE on 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.

See plans

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.