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.
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
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:
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.
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.
A DAX formula is comprised of an equal sign followed by a function or expression.
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])
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.
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.
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.
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.
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:
You can create formulas in PowerPivot either in calculated columns or in measures.
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.
A measure is a formula that is created specifically for use in a PivotTable (or PivotChart) that uses PowerPivot data. Measures can be based on standard aggregation functions, such as COUNT or SUM, or you can define your own formula by using DAX. A measure is used in the Values area of a PivotTable. If you want to place calculated results in a different area of a PivotTable, use a calculated column instead.
To create a measure, you must first add a PivotTable or Pivot Chart to your PowerPivot workbook. When you define a formula for a measure, nothing happens until you drop the measure into a PivotTable. When you add the measure, the formula is evaluated for each cell in the Values area of the PivotTable. Because a result is created for each combination of row and column headers, the result for the measure can be different in each cell.
The definition of the measure that you create is saved with its source data table. It appears in the PowerPivot Field List and is available to all users of the workbook.
For more information, see PowerPivot Measures.