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

What is DAX?

DAX is a formula language (decoded as Data Analysis eXpressions - expressions for data analysis), which was developed by the Microsoft company. 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 normal Excel functions, but can only be used in PowerPivot. As you know, ordinary Pivot tables can contain calculation fields, but only simple arithmetic operations - addition-subtraction and multiplication-division - can be performed in these fields. To increase the functionality of calculation fields, DAX was created. In general, DAX is not a complicated 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 strong> can refer only to entire tables and their columns.

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

Basic functions of DAX

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

  • Filter functions
  • Mathematical functions
  • Statistical functions
  • Boolean functions
  • Date and time functions
  • Text functions
  • Information functions
  • Calculations in PowerPivot

    In PowerPivot the user can create calculated fields of two types: Calculated Columns and Measures.

    Calculated column - an additional column that is created by the user in any table loaded in PowerPivot;

    Measure (Measure or Calculated field) is a calculation field in a summary table. The Measure is used in the Value area of the pivot table. To place the calculated results in other areas of the pivot table, you need to use the Calculated column.

    The same formulas can behave differently, depending on whether they are used in a calculated column or a measure. If a formula is used in a calculated column, it applies to each row of the column throughout the table. The value may depend on the context of the strings. To a degree, the calculation result 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.

    Context in DAX formulas

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

    So, in DAX there are three types of context: filter context, row 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.

    Line 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.

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

    Related Articles:

  • Application for expanding the capabilities of PivotTables (PowerPivot)
  • DAX-Lesson 2. CALCULATE function