Excel DAX - Syntax for DAX Formulas

DAX stands for Data Analysis Expressions. Hence, the objective of DAX is to construct DAX formulas that enable you to perform data analysis activities.

• A DAX formula always starts with an equals sign (=).

• After the equals sign, you can provide an expression that evaluates to a scalar value, or an expression that can be converted to a scalar value. These include the following −

  • A scalar constant.

  • An expression that uses a scalar operator (+,-,*, /,>=, &&, etc.)

  • References to columns or tables.

  • Operators, constants, and values provided as part of an expression.

  • The result of a DAX function and its required arguments. Some DAX functions return a table instead of a scalar value, and must be wrapped in a DAX function that evaluates the table and returns a scalar value. Unless the table is a single column, single row table, it is treated as a scalar value.

  • Most DAX functions require one or more arguments, which can include tables, columns, expressions and values. However, some DAX functions, such as PI, do not require any arguments, but always require parentheses to indicate the null argument. For e.g., you must always type PI (), but not PI. You can also nest DAX functions within other DAX functions.

  • Expressions. An expression can contain any or all of the following: operators, values, and references to columns.

Important Note − DAX formulas can behave differently depending on whether they are used in a calculated column or in a calculated field. You must always be aware of the context and how the data that you use in the DAX formula is related to other data that might be used in the calculation. You will learn more about this in the chapter – DAX Context.

Examples

• =[First Name] & [Last Name]
• =CONCATENATE ([First Name], [Last Name])
• Profit:=[Sales] – [Costs]

The first two are DAX formulas for calculated columns, whereas the third one is a DAX formula for a calculated field.