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

What is DAX?

DAX - language of formulas (decoded as Data Analysis eXpressions - expressions for data analysis), which was developed by Microsoft. This language is not independent, but intended only for automating calculations in the PowerPivot application for MS Excel .

In short, DAX are 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 you know, ordinary Summary tables may contain calculation fields (analog Measure in DAX), however, only simple arithmetic operations - addition-subtraction and multiplication-division - can be performed in these fields. So, in order to increase the functionality of calculation fields, DAX was created.

In general, DAX is not a complex language, as it may seem at first glance, you just need to understand the principles of its operation. The difference between Excel formulas and DAX formulas is that in Excel we operate on individual cells and ranges, while in DAX we can only access entire tables and their columns.

To get started with the DAX code, we need set PowerPivot for Excel 2010 or go to the tab of the same name in Excel 2013 .

Main functions of DAX

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 calculation fields of two types:

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

The same formulas can behave differently, depending on whether they are used in settlement column or in measures . If the formula is used in calculation column , it applies to each column row throughout the table. The value may depend on the context of the strings. IN measures the result of the calculation is even more context-dependent. This means that the design of the PivotTable and the choice of row and column headings affect the values used in the calculations.

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

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

Context in formulas DAX

In the language DAX 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 a pivot table, relationships between tables, and filters in formulas. The context allows for dynamic analysis. When building and troubleshooting formulas, it's important to understand the context's purpose.

So there are three types of context in DAX: the filter context , string context and request context .

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

String context - actually corresponds to the concept of the current line. If a calculated column is created, the values in each individual row and the values in the columns related to the current row serve as the context of the row.

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

Related Articles:

• Add-on for expanding the capabilities of Pivot Tables (PowerPivot)