Measures Template


The MeasuresTemplate template class creates measures based on the template in the following Measure Template Definition definition.

The template identifies column, table, and measure dependencies by using specific annotations.

{
  "_comment": "Standard Time Intelligence",
  "TargetTable": {
    "SQLBI_CalendarType": "Calendar"
  },
  "TemplateAnnotations": {
    "SQLBI_Template": "TimeIntelligence",
    "SQLBI_TimeIntelligence": "Standard",
    "SQLBI_CalendarType": "Calendar"
  },
  "MeasureTemplates": [...]
}

Measure Template Definition

TargetTable

List of annotations that must be matched at the table level in order to apply the measure template. See Annotations for standard annotations used.

TemplateAnnotations

List of annotations applied to the measures generated by the template. By looking at these annotations it is possible to identify and modify/remove the measures created by a measure template. For example, when a Time Intelligence template is applied to a model, the measures generated by a previous execution of a Time Intelligence template are removed. See Annotations for standard annotations used.

MeasureTemplates

List of Measure Template objects, one for each template item.

Measure Template

A measure template item defines how to create a measure for the template.

  "MeasureTemplates": [
    {
      "Name": "_ShowValueForDates",
      "IsHidden": true,
      "IsSingleInstance": true,
      "MultiLineComment": [
        "Returns TRUE if the period selected is earlier",
        "than the last period with data."
      ],
      "MultiLineExpression": [
        "VAR __LastDateWithData =",
        "    CALCULATE (",
        "        @@GETMAXDATE(),",
        "        REMOVEFILTERS ()",
        "    )",
        "VAR __FirstDateVisible =",
        "    MIN ( @_C-SQLBI_AttributeTypes-Date_@ )",
        "VAR __Result =",
        "    __FirstDateVisible <= __LastDateWithData",
        "RETURN",
        "    __Result"
      ]
    },
    {
      "Name": "YTD",
      "DisplayFolder": "To-date total",
      "MultiLineExpression": [
        "IF (",
        "    [_ShowValueForDates],",
        "    CALCULATE (",
        "        @@GETMEASURE(),",
        "        DATESYTD ( @_C-SQLBI_AttributeTypes-Date_@ )",
        "    )",
        ")"
      ]
    },
    ...
  ]

Name

Name of the measure template. if IsSingleInstance is false, this is the name applied as a prefix or suffix to the existing measure name when the new measure is created from the template. For example, YTD for Sales Amount will create YTD Sales Amount or Sales Amount YTD.

FormatString

(optional) Format String of the measure.

IsHidden

true if the measure is hidden.

IsSingleInstance

true if the measure must be created in a single instance and not for every measure. For example, the _ShowValueForDates measure is created only once by the time intelligence template and used by other measures created by the same template.

DisplayFolder

Name of the display folder.

Description

Description of the measure.

Annotations

List of annotations applied to the measure.

Expression

DAX expression defined in a single line. If defined, Expression has precedence over MultiLineExpression.

MultiLineExpression

Array of strings that define a comment in multiple lines. If Expression is defined, then MultiLineExpression is ignored.

Comment

Single line comment. If defined, Comment has precedence over MultiLineComment.

MultiLineComment

Array of strings that define a DAX expression in a multiple lines. If Comment is defined, then MultiLineComment is ignored.

Last update: Oct 13, 2024