Custom Template Table

The CustomTemplateTable defines the configuration of a custom table. This class is the foundation of the Custom Date Table.

Steps

Array of DaxExpression objects defining explicit table steps required by other expressions. For example, the Date table usually creates a __Calendar step that defines the range of dates using the @@GETCALENDAR() placeholder.

  "Steps": [
    {
      "Name": "__Calendar",
      "Expression": "@@GETCALENDAR()"
    }
  ]

DaxExpression

Common properties of the base DaxExpression object used by Steps, GlobalVariables, RowVariables, and Columns.

The code for the DAX expression is defined in either the Expression or MultiLineExpression property.

The content of the DAX expression must use a double underscore ( __ ) as a prefix for global and row variables, and a single underscore ( _ ) as a prefix for local variables defined and referenced within the same DAX expression.

Name

Name of the DAX expression, corresponding to the name of the step/variable/column.

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.

GlobalVariables

Array of objects derived from DaxExpression defining global variables that can be used by any following step. The global variables cannot have dependencies on other Steps of the template. Every global variable name must start with a double underscore prefix ( __ ).

IsConfigurable

This additional property extends the DaxExpression object for a global variable specifying whether the variable should be configurable (true) or not (false). The global variables with IsConfigurable set to true are included in an initial section of the table expression, so that they are easier to change by manually editing the DAX expression that defines the calculated table

RowVariables

Array of DaxExpression objects that define local variables for each row of the generated table. The expression can reference other variables defined in RowVariables and GlobalVariables. The template engine automatically arrange the right definition order evaluating the dependencies. Every row variable name must start with a double underscore prefix ( __ ).

Columns

Array of objects derived from DaxExpression defining the columns of the table generated by the template. As a naming convention, every column name should start with the @ prefix if the column is temporary to the calculation and must not be exposed in the final table.

If an expression references a temporary column, there are additional steps to create the column, reference it in following expressions, and then ignore it in the generated table.

DataType

Data type of the column:

• String
• Int64
• Double
• DateTime
• Decimal
• Boolean

FormatString

(optional) Format String of the column.

IsHidden

true if the column is hidden.

IsTemporary

true if the column is temporary. Temporary column should follow the naming convention starting with the @ symbol.

RequiresHolidays

true if the column requires an Holidays table. If the Holidays table is not enabled, the column is not included in the generated table.

SortByColumn

Name of the column to use in the Sort By property. For example, a Month column usually has MonthNumber in the SortByColumn property.

DisplayFolder

Name of the display folder.

DataCategory

Data category using standard definitions:

• Years
• Quarters
• QuarterOfYear
• Months
• MonthOfYear
• MonthOfQuarter
• Weeks
• WeekOfQuarter
• WeekOfYear
• DayOfWeek
• DayOfMonty
• DayOfQuarter
• DayOfYear
• PaddedDateTableDates

Description

Description of the expression. Can be used in comments for steps and variables.

Step

Specifies a column defined in the specified step. For example, the Date column of a Date table is usually defined in a __Calendar step:

  "Columns": [
    {
      "Name": "Date",
      "DataType": "DateTime",
      "Step": "__Calendar"
    }

AttributeType

Specify a single attribute type assigned to the column. When specified, it creates a list with a single attribute in AttributeTypes, ignoring the AttributeTypes definition.

  "Columns": [
    {
      "Name": "Date",
      "DataType": "DateTime",
      "Step": "__Calendar",
      "DataCategory": "PaddedDateTableDates",
      "AttributeTypes": "Date"
    },

AttributeTypes

Specify a list of attribute types assigned to the column. It is ignored if AttributeType is defined.

  "Columns": [
    {
      "Name": "Date",
      "DataType": "DateTime",
      "Step": "__Calendar",
      "DataCategory": "PaddedDateTableDates",
      "AttributeTypes": [
        "Date",
        "FiscalDate"
      ]
    }

Annotations

List of annotations added to the column. The annotations might be required by measure templates to identify measures, columns, and tables referenced by the template. For example, the filter safe columns in the time intelligence template are marked with the SQLBI_FilterSafe attribute set to true:

  "Columns": [
      {
        "Name": "IsWorking",
        "Expression": "__IsWorkingDay",
        "DataType": "Boolean",
        "RequiresHolidays": true,
        "IsHidden": true,
        "AttributeType": "IsWorkingDay",
        "Annotations": {
          "SQLBI_FilterSafe": true
        }
      }

Hierarchies

Array of objects defining user hierarchies of the table. Each hierarchy has the following properties.

  "Hierarchies": [
    {
      "Name": "Calendar",
      "Levels": [
        {
          "Name": "Year",
          "Column": "Year"
        },
        {
          "Name": "Month",
          "Column": "Year Month"
        },
        {
          "Name": "Date",
          "Column": "Date"
        }
      ]
    },
    {
      "Name": "Fiscal",
      "Levels": [
        {
          "Name": "Year",
          "Column": "Fiscal Year"
        },
        {
          "Name": "Month",
          "Column": "Year Month"
        },
        {
          "Name": "Date",
          "Column": "Date"
        }
      ]

Name

Name of the hierarchy.

Description

Description of the hierarchy.

Levels

Array of objects defining the levels of the hierarchy. Each level has the following properties.

Name

Name of the hierarchy level.

Column

Corresponding column name of the hierarchy level. The column name is the simple name, it is not a fully qualified name.

Description

Description of the hierarchy level.

Annotations

List of annotations added to the table. The annotations might be required by measure templates to identify measures, columns, and tables referenced by the template.

FormatPrefixes

Array of strings used as prefix/suffix in formatted name of attribute values. The purpose of this definition is to create a list of names that can be translated in localized versions.

 "FormatPrefixes": [ "Q", "FY", "FQ", "FM", "FW" ]