The Data Analysis Expressions (DAX)
The Data Analysis eXpressions (DAX)
language provides a specialised syntax for querying Analysis Services tabular model.
- DAX is NOT a programming language.
- DAX is a formula language.
- You can use DAX to define custom calculations for Calculated Columns and for Calculated Fields (measures) in Analysis Services Tabular Model.
- DAX was developed by the SQL Server Analysis Services team at Microsoft as part of Project Gemini and released in 2009 with the first version of the PowerPivot for Excel 2010 Add-in. Both DAX and Multidimensional Expressions (MDX) can be used to query PowerPivot and Tabular models, however only MDX may be used to query multidimensional SSAS models (cubes) in versions of SSAS up to SQL Server 2012 RTM.
- Future versions of SSAS (both multidimensional & tabular models) will support DAX natively.
- The Data Analysis Expressions (DAX) language is a library of functions and operators that can be combined to build formulas and expressions.
“The best information comes from those who use DAX”. DAX, is a formula expression language used to define calculations in PowerPivot for Excel® workbooks and Tabular model projects authored in SQL Server Data Tools. DAX functions provide extensive filtering to calculate on data across multiple tables, work with relationships ,and perform dynamic aggregation.
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.
DAX can compute values for seven data types:
- Date (datetime)
- TRUE/FALSE (Boolean)
- BLOB (binary large object)
DAX has a powerful type-handling system so that you do not have to worry much about data types. When you write a DAX expression, the resulting type is based on the type of the terms used in the expression and on the operator used. Type conversion happens automatically during the expression evaluation.
An expression evaluates the operators and values in a specific order. All expressions always begin with an equal sign (=). The equal sign indicates that the succeeding characters constitute an expression.
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.
Following the equal sign are the elements to be calculated (the operands), which are separated by calculation operators. Expressions are always read from left to right, but the order in which the elements are grouped can be controlled to some degree by using parentheses.
If you combine several operators in a single formula, the operations are ordered according to the following table. If the operators have equal precedence value, they are ordered from left to right. For example, if an expression contains both a multiplication and division operator, they are evaluated in the order that they appear in the expression, from left to right.
- ^ (exponentiation)
- – (negation)
- * and / (multiplication and division)
- ! (NOT; unary operator)
- + and – (addition and subtraction)
- & (connects two strings of text; concatenation)
- =< ><=>=<> (comparison)
To change the order of evaluation, you should enclose in parentheses that part of the formula that must be calculated first. (i believe you understood about this logic)
Context in DAX Formulas
Context enables you to perform dynamic analysis, in which the results of a formula can change to reflect the current row or cell selection and also any related data. Understanding context and using context effectively are very important for building high-performing formulas, dynamic analyses, and for troubleshooting problems in formulas.
- Row 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: 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: 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.
DAX Function Reference – Detailed information such as syntax, parameters, return values, and examples for each of the over 200 functions used in Data Analysis Expression (DAX) formulas.
- Date and Time Functions (DAX)
- Time Intelligence Functions (DAX)
- Filter Functions (DAX)
- Information Functions (DAX)
- Logical Functions (DAX)
- Math and Trig Functions (DAX)
- Parent/Child Functions (DAX)
- Statistical Functions (DAX)
- Text Functions (DAX)
- Other Functions (DAX)
- New DAX functions!