Getting Started with Data Analysis Expressions (DAX)
The Data Analysis Expressions (DAX) language is a formula language that allows users to define custom calculations in PowerPivot tables (calculated columns) and in Excel PivotTables (measures). DAX includes some of the functions that are used in Excel formulas, and additional functions that are designed to work with relational data and perform dynamic aggregation.
This section explains the following concepts:
Where to use DAX formulas
How to create DAX formulas
Types of operations you can perform with DAX
Overview of DAX Formulas
DAX formulas are very similar to Excel formulas. To create one, you type an equal sign, followed by a function name or expression, and any required values or arguments. Like Excel, DAX provides a variety of functions that you can use to work with strings, perform calculations using dates and times, or create conditional values.
However, DAX formulas are different in the following important ways:
A DAX function always references a complete column or a table. If you want to use only particular values from a table or column, you can add filters to the formula.
If you want to customize calculations on a row-by-row basis, PowerPivot provides functions that let you use the current row value or a related value to perform calculations that vary by context.
DAX includes a type of function that returns a table as its result, rather than a single value. These functions can be used to provide input to other functions, thus calculating values for entire tables or columns.
Some DAX functions provide time intelligence, which lets you create calculations using meaningful ranges of dates, and compare the results across parallel periods.
For additional information about these and other concepts that are new in DAX, see Key Concepts in DAX.
Where to Use Formulas
You can use DAX formulas either in PowerPivot tables, or in PivotTables in Excel:
You can use formulas in calculated columns, by adding a column and then typing an expression in the formula bar. You create these formulas in the PowerPivot window. For more information, see Create a Calculated Column.
You can use formulas in measures. You create these formulas in Excel, by clicking Add Measure in an existing PowerPivot PivotTable or PivotChart. For more information, see Create a Measure.
The same formula can behave differently depending on whether the formula is used in a calculated column or a measure. In a calculated column, the formula is always applied to every row in the column, throughout the table. Depending on the row context, the value might change. In a measure, however, the calculation of results is strongly dependent on context. That is, the design of the PivotTable and the choice of row and column headings affects the values that are used in calculations. For more information, see Context in DAX Formulas.
Creating Formulas by Using the Formula Bar
PowerPivot, like Excel, provides a formula bar to make it easier to create and edit formulas, and AutoComplete functionality, to minimize typing and syntax errors.
To enter a name of a table Begin typing the name of the table. Formula AutoComplete provides a dropdown list containing valid names that begin with those letters.
To enter the name of a column Type a bracket, and then choose the column from the list of columns in the current table. For a column from another table, begin typing the first letters of the table name, and then choose the column from the AutoComplete dropdown list.
For a walkthrough of how to build formulas, see Building Formulas for Calculated Columns and Measures.
Tips for Using AutoComplete
You can use Formula AutoComplete in the middle of an existing formula with nested functions. The text immediately before the insertion point is used to display values in the drop-down list, and all of the text after the insertion point remains unchanged.
Defined names that you create for constants do not display in the AutoComplete drop-down list, but you can still type them.
PowerPivot does not add the closing parenthesis of functions or automatically match parentheses. You must make sure that each function is syntactically correct or you cannot save or use the formula.
Using Multiple Functions in a Formula
You can nest functions, meaning that you use the results from one function as an argument of another function. You can nest up to 64 levels of functions in calculated columns. However, nesting can make it difficult to create or troubleshoot formulas.
Many PowerPivot functions are designed to be used solely as nested functions. These functions return a table, which cannot be directly saved as a result to the PowerPivot workbook; it must be provided as input to a table function. For example, the functions SUMX, AVERAGEX, and MINX all require a table as the first argument.
Note
Some limits on nesting of functions exist within measures, to ensure that performance is not affected by the many calculations required by dependencies among columns.
Types of DAX Functions
DAX provides functions that have the same functionality and names as the Excel functions that you are already familiar with. However, the functions have been modified to use DAX data types and to work with tables and columns. In addition, DAX provides many specialized functions for specific purposes, such as lookups based on relationships, the ability to iterate over a table to perform recursive calculations, and calculations utilizing time intelligence.
This section provides an overview of the types of functions supported in the DAX language.
Date and Time Functions
The date and time functions in DAX are similar to date and time functions in Microsoft Excel. However, DAX functions are based on the datetime data types used by Microsoft SQL Server. For more information, see Date and Time Functions (DAX).
Filter Functions
The filter functions in DAX let you return specific data types, look up values in related tales, and filter by related values. The lookup functions work by using tables and relationships, like a database. The filtering functions let you manipulate data context to create dynamic calculations. For more information, see Filter Functions (DAX).
Information Functions
An information function looks at the cell or row that is provided as an argument and tells you whether the value matches the expected type. For example, the ISERROR function returns TRUE if the value that you reference contains an error. For more information, see Information Functions (DAX).
Logical Functions
Logical functions act upon an expression to return information about the values in the expression. For example, the TRUE function lets you know whether an expression that you are evaluating returns a TRUE value. For more information, see Logical Functions (DAX).
Mathematical and Trigonometric Functions
The mathematical functions in DAX are very similar to the Excel mathematical and trigonometric functions. Some minor differences exist in the numeric data types used by DAX functions. For more information, see Math and Trigonometric Functions (DAX).
Statistical Functions
PowerPivot provides statistical functions that perform aggregations, similar to those in Microsoft Excel. In addition to creating sums and averages, or finding the minimum and maximum values, in DAX you can also filter a column before aggregating or create aggregations based on related tables. For more information, see Statistical Functions (DAX).
Text Functions
The text functions in DAX are very similar to their counterparts in Excel. You can return part of a string, search for text within a string, or concatenate string values. DAX also provides functions for controlling the formats for dates, times, and numbers. For more information, see Text Functions (DAX).
Time Intelligence Functions
The time intelligence functions provided in DAX let you create calculations that use built-in knowledge about calendars and dates. By using time and date ranges in combination with aggregations or calculations, you can build meaningful comparisons across comparable time periods for sales, inventory, and so on. For more information, see Time Intelligence Functions (DAX).
See Also
Other Resources
Data Analysis Expressions (DAX) Language