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" ]