DAX Syntax

Basic Structure of a DAX Expression

A DAX expression is a formula that can be used in measures, calculated columns, and row-level security rules. Most DAX expressions start with an equals sign (=).

= Expression

Expressions can be simple, like a direct reference to a column, or complex, involving multiple functions, operators, and references.

Operators

DAX supports various operators for performing calculations:

• Arithmetic Operators: +, -, *, /, ^
• Comparison Operators: =, <, >, <=, >=, <>
• Text Concatenation Operator: &
• Logical Operators: AND, OR, NOT

Functions

DAX provides a rich library of built-in functions categorized by their purpose:

• Aggregation Functions: SUM, AVERAGE, MIN, MAX, COUNT, DISTINCTCOUNT
• Date and Time Functions: DATE, YEAR, MONTH, DAY, TODAY, NOW
• Filter Functions: CALCULATE, FILTER, ALL, RELATED
• Logical Functions: IF, SWITCH, TRUE, FALSE
• Information Functions: ISBLANK, ISERROR
• Math and Engineering Functions: ABS, ROUND, POWER
• Text Functions: CONCATENATE, LEFT, RIGHT, LEN, FORMAT
• Table Manipulation Functions: ADDCOLUMNS, SUMMARIZE, VALUES
• Time Intelligence Functions: TOTALYTD, SAMEPERIODLASTYEAR

For a comprehensive list of DAX functions, refer to the DAX Functions Reference.

Expressions and Literals

DAX expressions can include literals (constant values) and references to columns, measures, or tables.

Literals

• Numbers: 123, 45.67
• Strings: "Hello World", 'Sales'[ProductName]
• Dates: DATE(2023, 10, 27), {2023-10-27}
• Booleans: TRUE, FALSE
• Blank: BLANK()

Column and Table References

References to columns in a table are typically made using the following format:

'TableName'[ColumnName]

For example:

= 'Sales'[Quantity] * 'Sales'[UnitPrice]

Table names containing spaces or special characters must be enclosed in single quotes.

Measures and Calculated Columns

DAX expressions are primarily used in two ways within a data model:

Measures

Measures are dynamic calculations that are evaluated at query time based on the current filter context. They are often used for aggregations and business metrics.

Total Sales = SUM(Sales[SalesAmount])

Calculated Columns

Calculated columns are new columns added to a table. Their values are computed row by row during data refresh and are stored in the model.

Full Name = 'Customers'[FirstName] & " " & 'Customers'[LastName]

Line Total = 'Sales'[Quantity] * 'Sales'[UnitPrice]

Variables in DAX

DAX supports the use of variables within expressions using the VAR keyword. Variables can improve readability and performance by allowing you to define intermediate results.

TotalRevenue =
VAR BaseRevenue = SUM(Sales[SalesAmount])
VAR DiscountAmount = SUM(Sales[DiscountAmount])
RETURN
    BaseRevenue - DiscountAmount
                

Filter Context and Evaluation

Understanding filter context is crucial for writing effective DAX. DAX formulas are evaluated in a context that is determined by filters applied from the report, other measures, and the data model itself.

Functions like CALCULATE are powerful tools for manipulating and modifying the filter context in which an expression is evaluated.

DAX Best Practices

Adhering to best practices ensures your DAX formulas are efficient, readable, and maintainable.

• Use Variables: For complex calculations or to avoid repeating expressions.
• Choose Appropriate Functions: Understand the performance implications of different functions (e.g., SUMX vs. SUM).
• Optimize Filter Context: Use functions like ALL, ALLEXCEPT, and CALCULATE strategically.
• Name Measures Clearly: Use descriptive names for your measures.
• Format Your Code: Indentation and spacing improve readability.