Calculation Groups
Calculation groups can apply specific calculations onto existing DAX measures. For example, calculation groups can implement time intelligence calculations like year-to-date, year-over-year, and currency conversion – or, they can enable the selection of a measure in an existing report through a slicer.
Calculation groups are easy to use; however, achieving the right design for a model with calculation groups can be challenging when you create multiple calculation groups or when you use calculation items in measures. For this reason, we provide best practices to help you avoid any issues. Deviating from these best practices requires a deep understanding of how calculation groups are designed, to ensure your model is robust.
You can find more details in these articles about calculation groups in DAX:
-
- Introducing Calculation Groups
- Understanding Calculation Groups
- Understanding the Application of Calculation Items
- Understanding Calculation Groups Precedence
- Controlling Format Strings in Calculation Groups
- Avoiding Pitfalls in Calculation Groups Precedence
- Using calculation groups to switch between dates
- Using calculation groups to selectively replace measures in DAX expressions
- Understanding the interactions between composite models and calculation groups
If you want a quick start, here is a video showing step-by-step how to create your first calculation group in Power BI Desktop.
Hypothetical syntax for the definition of calculation groups
Calculation groups can be created in a Tabular model using Visual Studio or Tabular Editor. In order to simplify the definition of calculation groups in articles and documentation, we use a hypothetical syntax that may also be used in a future scripting language.
WARNING: Please note the following syntax is not currently supported in DAX tools.
We use it as a scripting language to simplify the sharing of DAX code related to calculation groups.
Assuming you have the following calculation group:
- Calculation Group: Time Intelligence
- Visible column: Calc
- Calculation items: Current, YTD, YOY, YOY %
The scripting we use to define these calculation items is the following:
CALCULATIONITEM 'Time Intelligence'[Calc]."Current" = SELECTEDMEASURE (), FormatStringExpression = "#,#" CALCULATIONITEM 'Time Intelligence'[Calc]."YTD" = CALCULATE ( SELECTEDMEASURE (), DATESYTD ( 'Date'[Date] ) ) CALCULATIONITEM 'Time Intelligence'[Calc]."YOY" = SELECTEDMEASURE () - CALCULATE ( SELECTEDMEASURE (), SAMEPERIODLASTYEAR ( 'Date'[Date] ) ) CALCULATIONITEM 'Time Intelligence'[Calc]."YOY" = VAR CurrentValue = SELECTEDMEASURE () VAR PreviousYearValue = CALCULATE ( SELECTEDMEASURE (), SAMEPERIODLASTYEAR ( 'Date'[Date] ) ) VAR DiffValue = CurrentValue - PreviousYearValue RETURN DIVIDE ( DiffValue, PreviousYearValue ), FormatStringExpression = "0.00%"