Excel DAX - Naming Requirements

Data Model comprises of tables between which relationships exist. It is the database for Power Pivot. Each table is stored on a separate tab in the Data Model and the table name is given as the tab name. Each table will have columns, representing the data fields. The column names will be displayed as the data fields in the PivotTable Fields list.

  • The tables in the Data Model must have unique names.

  • The table names are case insensitive. i.e., Results, RESULTS, results – all these represent the same table.

  • Each calculated column and calculated field that you add to a Data Model must be associated with a specific table.

    • When you create a calculated column, you will do it within a table, thus associating it implicitly with that table.

    • When you create a calculated field, you will create it - either within a table in the Power Pivot window – or from the Excel window specifying the name of the table where the calculated field definition should be stored.

In both the cases, the calculated field is termed as explicit calculated field and is associated with a table.

DAX Naming Requirements Across Tables

  • You need to specify the table name in a DAX formula if the column is from a different table than the current table.

  • Table names must be enclosed in single quotation marks if they contain spaces, other special characters, or any non-English alphanumeric characters.

DAX Naming Requirements – Calculated Fields

  • The names of the calculated fields must always be in brackets.

  • The names of the calculated fields can contain spaces.

  • Each calculated field name must be unique within a Data Model. The table name is optional in front of a calculated field name as it is always associated with a specific table.

DAX Naming Requirements – Columns

  • Column names must be unique within a table. However, different tables can have columns with the same names.

  • The columns can be referenced with unqualified column names if there is no name conflict. An unqualified column name is just the column name, enclosed in brackets. For e.g. [Medal]. When you are referencing a scalar value from the same row of the current table, you can use the unqualified column name.

  • However, if the same column name is used in more than one table, then the column has to be referenced with fully qualified column name. The fully qualified column name is the table name, followed by the column name in square brackets. For e.g. Results[Medal].

  • When you use a column as an input to a DAX function, it is a good practice to fully qualify the column name. The following DAX functions require column names to be fully qualified −

    • VALUES.

    • ALL and ALLEXCEPT.

    • CALCULATE and CALCULATETABLE - in a filter argument.

    • RELATEDTABLE.

    • DAX time intelligence functions.

DAX Naming Requirements – Reserved Keywords

If the name that you use for a table is the same as an Analysis Services reserved keyword, an error is raised and you must rename the table. However, you can use the keywords in the object names, if the object name is enclosed in brackets (for columns) or quotation marks (for tables).

Quotation marks can be represented by several different characters, depending on the application used. Hence, if you paste formulas from an external document or web page, make sure to check the ASCII code of the character that is used for opening and closing quotes, to ensure that they are the same. Otherwise, DAX may be unable to recognize the symbols as quotation marks, making the reference invalid.

DAX Fully Qualified Names – Special Cases

If a table name contains spaces or reserved keywords or disallowed characters, you must enclose the table name within single quotation marks. For e.g. ‘East_Sales'[Amount].

You must enclose the table names in quotation marks, if the name contains any characters outside the ANSI alphanumeric character range, regardless of whether your locale supports the character set or not. For e.g., if a table name is written in Cyrillic characters, such as ‘Таблица’, the table name must be enclosed in quotation marks even though it does not contain spaces.

You can use the formula AutoComplete feature, as it will then be just a matter of selecting the fully qualified names of columns from the dropdown list, making your job easier and error proof.

Exclusion of Special Characters in Names

The following characters and character types are not valid in the names of tables, columns, or calculated fields −

Leading or trailing spaces, unless the spaces are enclosed by name delimiters, brackets, or single apostrophes. Control characters.

The following characters are not valid in the names of Power Pivot objects −

.,;':/\*|?&%$!+=()[]{}<>

Common DAX Formula Rules

The DAX formula syntax depends on the type of operation it can perform and varies greatly if it contains DAX functions. Following rules apply to all the DAX formulas −

  • DAX formulas cannot modify or insert individual values in tables.

  • You cannot create calculated rows by using DAX. You can create only calculated columns and calculated fields.

  • When defining calculated columns, you can nest functions to any level (a maximum being 64 that is a bit too much).

  • DAX has several functions that return a table. Typically, you use the values returned by these DAX functions as an input to other DAX functions, which require a table as an input.

excel_dax_syntax.htm
Advertisements