Data Analysis eXpression, calculated columns, and measures

Data Analysis eXpression (DAX) is a query language for tabular models of Microsoft, such as PowerPivot and SSAS Tabular. DAX was introduced in 2010 with PowerPivot and enhanced in SSAS Tabular 2012. The structure of a DAX command is very simple, but a number of built-in functions in DAX is enough to create complex expressions to answer business requirements.

DAX is the language used for creating calculated columns and measures. As you saw in the previous sections, calculated columns are columns added in each table. The value of the calculated columns will be calculated at the time of processing and will be stored in memory. Measures also use DAX expressions for calculations. Measures will be calculated on the fly; their value won't be stored anywhere and will just be visible to the user after calculation. There are two main kinds of evaluation contexts in DAX: row context and filter context. Row context means the current row and is useful for writing expressions that work with the current row, like many of calculated columns. Filter context is a set of records filtered by criteria and is good for creating measures based on a subset of records.

DAX is not the same as MDX. MDX is an expression language that is used to query multidimensional models, as you saw in the previous chapter. MDX supports powerful query features to provide a multidimensional result set. However, DAX is used to query tabular models only. It has many built-in functions that can be used in created measures and calculated members. An SSAS Tabular model works with DAX as the main query language, but it also supports MDX clients (we will go in more details of it in the Storage Modes section).