DAX-Lesson 1. What is DAX. Basic concepts.

What is DAX?

DAX is a formula language (stands for Data Analysis eXpressions - expressions for data analysis), which was developed by Microsoft. This language is not standalone, but is intended only for automating calculations in the PowerPivot add-in for MS Excel.

In short, DAX is formulas for pivot tables. Most DAX functions have similar names and characteristics compared to regular Excel functions, but can only be used in PowerPivot. As known, regular Pivot tables can contain calculated fields (an analogue of Measure in DAX), however, these fields can only perform simple arithmetic operations - addition-subtraction and multiplication-division. So, to increase the functionality of calculated fields, DAX was created.

Overall, DAX is not a complex language, as it might seem at first glance; you just need to understand its operating principles. The difference between Excel formulas and DAX formulas is that in Excel we operate with individual cells and ranges, while in DAX we can only refer to entire tables and their columns.

To start working with DAX code, we need to install PowerPivot for Excel 2010 or go to the tab with the same name in Excel 2013.

Main DAX functions

As in Excel, DAX functions belong to certain categories, namely:

• Filter functions
• Mathematical functions
• Statistical functions
• Logical functions
• Date and time functions
• Text functions
• Information functions

Calculations in PowerPivot

In PowerPivot, the user can create calculated fields of two types:

• Calculated column (Calculated field) - an additional column that is created by the user in any table loaded into PowerPivot;
• Measure (Measure) - a calculated field in a pivot table. Measure is used in the Values area of the pivot table. To place the calculated results in other areas of the pivot table, you need to use Calculated column.

The same formulas can behave differently, depending on whether they are used in a calculated column or in a measure. If the formula is used in a calculated column, it is applied to each row of the column across the entire table. The value may depend on the row context. In a measure, the result of calculations depends even more on the context. This means that the pivot table design and the selection of row and column headers affect the values used in the calculations.

That is, in other words, when we create a Pivot table, then:

• In the calculated column of the source table, mathematical operations are first performed on the data (according to the formula written in this column), and then - the obtained results are grouped according to the Pivot table design;
• In a measure, on the other hand, data is first grouped according to the Pivot table design, and then - operations are performed on the newly created data groups (for example, on data grouped quarterly for a certain year). This is where DAX functions come into play.

Context in DAX formulas

In the DAX language, there is also such a concept as context, which is very important for understanding the functioning of formulas. Formulas in PowerPivot can be affected by filters applied in the pivot table, relationships between tables, and filters present in the formulas. Context allows for dynamic analysis. When building and troubleshooting formulas, it is important to understand the purpose of context.

So, in DAX there are three types of context: filter context, row context, and query context.

Filter context - is a set of values allowed in each column depending on the filter restrictions that apply to the row or that are defined by the filter criteria in the formula.

Row context - actually corresponds to the concept of the current row. If a calculated column is created, then the row context consists of the values in each individual row and the values in the columns related to the current row.

Query context - refers to a subset of data that is implicitly created for each cell of the pivot table depending on the row and column headers in the pivot table (pivot table layout design).

Articles on the topic:

• Add-in for expanding the capabilities of Pivot tables (PowerPivot)