The Data Analysis Expressions (DAX) language is a new PowerPivot 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.

For a list of every DAX Internet resource that we can find, see the DAX Survival Guide.

The syntax of DAX formulas is very similar to that of Excel formulas, using a combination of functions, operators, and values. Where DAX formulas differ from Excel formulas is that DAX functions work with tables and columns, not ranges, and let you do sophisticated lookups to related values and related tables. With DAX formulas, you can create aggregations that would ordinarily require in-depth knowledge of relational database schemas or OLAP concepts. Moreover, because calculations in DAX formulas utilize the highly optimized in-memory engine, you can rapidly look up and calculate values across very large columns or tables.

Read more about DAX operators in the DAX Operator Reference.

Original article (before wiki edits) was written by Jeannine Takaki, Microsoft SQL Server Analysis Services Technical Writer.

---

Types of DAX Functions

DAX provides functions that have the same functionality and names as the Excel functions that you might already be 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.

Read more about the following types of DAX functions on their individual pages:

 ↑ Back to top

---

Overview of DAX

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.

Where to Use Formulas

You can use DAX formulas either in PowerPivot tables, or in PivotTables in Excel:

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.

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.

Tips for Using AutoComplete

Basic DAX Syntax

A DAX formula is comprised of an equal sign followed by a function or expression.

• Functions perform operations such as concatenating or adding values, calculating sums or averages, or performing logical tests. Functions usually take some kind of argument, which might be a reference to a column or table. Functions can be nested inside other functions.
• An expression can be used to define a value that can be a literal value or constant, a Boolean test, or a reference to a column containing values. Boolean expressions can be used to define a filter condition, such as [Sales] > 100.
• Operators within expressions, such as a plus or minus sign, indicate how the values are to be compared or processed.
• Values that you use in formulas and expressions can be typed directly into the formula bar as part of an expression, or they can be obtained from other columns, tables, or formulas. However, you cannot reference only a few cells or a range of cells; DAX always works with complete columns or tables.

For example, the following formulas are all valid:

• =3

• ="Sales"

• ='All Sales'[Amount]

• =[Amount]*1.10

• =PI()

• ='FALSE' = 0

• =SUMX(FILTER(Sales,Region="Europe"),[SalesAmount])

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.

DAX Data Types

You can import data into a PowerPivot worksheet from many different data sources that might support different data types. When you import or load the data into a workbook and then use the data in calculations or in PivotTables, the data is converted to one of the PowerPivot data types.

The table data type is a new data type in DAX that is used as the input or output to many new functions. For example, the FILTER function takes a table as input and outputs another table that contains only the rows that meet the filter conditions. By combining table functions with aggregation functions, you can perform complex calculations over dynamically defined data sets.

Formulas and the Relational Model

The PowerPivot window is an area where you can work with multiple tables of data and connect the tables in a relational model. Within this model, tables are connected to each other by relationships, which let you create correlations with columns in other tables and create more interesting calculations. For example, you can create formulas that sum values for a related table and then save that value in a single cell. Or, to control the rows from the related table, you can apply filters to tables and columns.

Because you can link tables by using relationships, your PivotTables can also include data from multiple columns that are from different tables.

However, because formulas can work with entire tables and columns, you need to design calculations differently than you do in Excel.

• In general, a DAX formula in a column is always applied to the entire set of values in the column (never to only a few rows or cells).
• Tables in PowerPivot must always have the same number of columns in each row, and all rows in a column must contain the same data type.
• When tables are connected by a relationship, you are expected to make sure that the two columns used as keys have values that match, for the most part. Because PowerPivot does not enforce referential integrity, it is possible to have non-matching values in a key column and still create a relationship. However, the presence of blank or non-matching values might affect the results of formulas and the appearance of PivotTables.
• When you link tables in your workbook by using relationships, you enlarge the scope, or context, in which your formulas are evaluated. For example, formulas in a PivotTable can be affected by any filters or column and row headings in the PivotTable. You can write formulas that manipulate context, but context can also cause your results to change in ways that you might not anticipate.

Introduction to Context

Formulas in PowerPivot can be affected by the filters applied in a PivotTable, by relationships between tables, and by filters used in formulas. Context is what makes it possible to perform dynamic analysis. Understanding context is important for building and for troubleshooting formulas.

There are different types of context: row context, query context, and filter context.

Row context can be thought of as "the current row.” If you have created a calculated column, the row context consists of the values in each individual row and values in columns that are related to the current row. There are also some functions (EARLIER and EARLIEST) that get a value from the current row and then use that value while performing an operation over an entire table.

Query context refers to the subset of data that is implicitly created for each cell in a PivotTable, depending on the row and column headers.

Filter context is the set of values allowed in each column, based on filter constraints that were applied to the row or that are defined by filter expressions within the formula.

Updating the Results of Formulas

Data refresh and recalculation are two separate but related operations that you should understand when designing a data model that contains complex formulas, large amounts of data, or data that is obtained from external data sources.

Refreshing data is the process of updating the data in your workbook with new data from an external data source. You can refresh data manually at intervals that you specify. Or, if you have published the workbook to a SharePoint site, you can schedule an automatic refresh from external sources.

Recalculation is the process of updating the results of formulas and calculated columns in your workbook to reflect any changes to the formulas and to reflect changes in the underlying data. Recalculation can affect performance in the following ways:

• For a calculated column, the result of the formula must always be recalculated for the entire column, whenever you change the formula.
• For a measure, however, the results of a formula are not calculated until the measure is placed in the context of the PivotTable or PivotChart. The formula will also be recalculated when you change any row or column heading that affects filters on the data or when you manually refresh the PivotTable.

 ↑ Back to top

---

Measures and Calculated Columns

You can create formulas in PowerPivot either in calculated columns or in measures.

Calculated Columns

A calculated column is a column that you add to an existing PowerPivot table. Instead of pasting or importing values in the column, you create a DAX formula that defines the column values. If you include the PowerPivot table in a PivotTable (or PivotChart), the calculated column can be used as you would any other data column.

The formulas in calculated columns are much like the formulas that you create in Excel. Unlike in Excel, however, you cannot create a different formula for different rows in a table; instead, the DAX formula is automatically applied to the entire column.

When a column contains a formula, the value is computed for each row. The results are calculated for the column as soon as you create the formula. Column values are only recalculated if the underlying data is refreshed or if manual recalculation is used.

You can create calculated columns that are based on measures and other calculated columns.

Measures

 ↑ Back to top

---

See Also

• Wiki: List of Products and Related Topics
• PowerPivot Overview
• PowerPivot Measures
• DAX Survival Guide

---

TechNet Library

• Getting Started with DAX

• Key Concepts in DAX

• Referential Integrity, PowerPivot Relationships, and the Unknown Member

---

External Links

• PowerPivot page on TechNet
• PowerPivot Web site
• PowerPivot Videos Web page
• PowerPivot Videos on YouTube

---