DAX Syntax Reference

DAX Syntax Reference

This document provides a comprehensive reference for the Data Analysis Expressions (DAX) syntax used in SQL Server Analysis Services, Power BI, and Excel Power Pivot.

Core Concepts

DAX is a formula expression language used to create custom calculations in Analysis Services data models. Key components include:

• Measures: Dynamic calculations that respond to user interaction.
• Calculated Columns: Static columns added to a table, evaluated row by row.
• Calculated Tables: Tables created from DAX expressions.
• Row Context: The current row being evaluated within a table.
• Filter Context: The set of filters applied to the data model, affecting calculation results.

Syntax Structure

DAX formulas generally follow this structure:

<Formula> = <Expression>

Where:

• <Formula> is the name of the measure, calculated column, or calculated table.
• <Expression> is a DAX expression that calculates a single value or a table.

Common DAX Functions and Their Syntax

Aggregation Functions

These functions perform calculations across a set of values.

• SUM( <column> ): Returns the sum of all numbers in a column.
• AVERAGE( <column> ): Returns the average of numbers in a column.
• COUNT( <column> ): Counts the number of rows containing numbers in a column.
• MAX( <column> ): Returns the largest value in a column.
• MIN( <column> ): Returns the smallest value in a column.

Logical Functions

These functions perform logical tests.

• IF( <logical_test>, <value_if_true>, <value_if_false> ): Returns one value if a logical expression is true and another if it is false.
• AND( <logical1>, <logical2> ): Returns TRUE if all arguments are TRUE.
• OR( <logical1>, <logical2> ): Returns TRUE if any argument is TRUE.

Text Functions

These functions manipulate text strings.

• CONCATENATE( <text1>, <text2> ): Joins two text strings.
• LEFT( <text>, <num_chars> ): Returns the specified number of characters from the start of a text string.
• RIGHT( <text>, <num_chars> ): Returns the specified number of characters from the end of a text string.
• LEN( <text> ): Returns the number of characters in a text string.

Date and Time Functions

These functions work with dates and times.

• DATE( <year>, <month>, <day> ): Creates a date.
• YEAR( <date> ): Returns the year of a date.
• MONTH( <date> ): Returns the month of a date.
• DAY( <date> ): Returns the day of a date.
• TODAY( ): Returns the current date.

Statistical Functions

These functions perform statistical calculations.

• RANK.EQ( <number>, <reference>, [<order>] ): Returns the rank of a number in a list.
• MEDIAN( <column> ): Returns the median of numbers in a column.

Common DAX Operators and Their Syntax

• Arithmetic Operators: +, -, *, /
• Comparison Operators: =, >, <, >=, <=, <>
• Text Concatenation Operator: &
• Parentheses for Grouping: ( )

For a complete list of DAX functions, operators, and detailed explanations, please refer to the official DAX Function Reference.