How hard is it to learn DAX? The article deals with the challenges of learning DAX as a BI language for the tabular model and the difference to the MDX language.

At a SQLPASS event for the introduction of Power BI and DAX, the MVP pointed out that Microsoft had classified the MDX language as so difficult for the user that Microsoft had decided to replace MDX with DAX.

In my perception, DAX is not necessarily easier than MDX, even after several years of working with DAX, I would rather say the opposite. It is true that there was always a big initial hurdle with MDX. But once this hurdle had been overcome, the language was almost easy to understand and intuitive to use due to its clear foundation. On the other hand, DAX gives the impression of being easier to understand in the first attempts, but the difficulties quickly become apparent. In the end, it is a big task to develop reliable KPIs, DAX code that delivers the expected result under all circumstances.

My experience shows that the complexity of the DAX language leads to challenges in teaching and generally in learning the language: the topics must be taught or learned iteratively, just as the manual The Definitive Guide To DAX does.

The goal of the typical target group for DAX, the Excel power users such as financial analysts, is to learn and develop in the shortest possible amount of time. Many power users are actually able to achieve this kind of effectiveness by:

The difficulties arise with certain circumstances:

1. A DAX KPI has been developed and obviously works. The KPI is deployed and users start to use the interactive capabilities of a PowerBI report, place the KPI in different visuals or with different data than in the development environment. Suddenly, the KPI displays incorrect values.

2. The objective of a KPI changes slightly. For example, the customer would like to see the previous week's value instead of the previous month's value. The resulting DAX code can now be anything but trivial. Additionally bad data models may turn into issues.

From my current perspective, there are no simple DAX expressions or simple DAX queries. Even a few lines of a DAX KPI must be understood with all the consequences; and that is a challenging task.

Complexity of DAX KPIs does not necessarily be caused by complex code or many lines of code, but can simply be caused by the variety of applications. Particularly complex is the function CALCULATE, a sticking point of the language. If you really understand CALCULATE, you are able to develop DAX.

The starting point in both languages - MDX and DAX - is the dimension data such as years and regions. In MDX, the primary goal for developers is to submit correct tuples from the dimension data, e.g. (2018, East). Simple aggregates such as sum or count aggregates follow the tupels automatically, since they are bound to the tuples.

In DAX, dimension data is used to implicitly or explicitly setup filter. Filters point to a bunch of fact rows. In contrast to MDX, the set of facts rows must be aggregated into a scalar in a further step by a user-defined rollup formula. Only the scalar can be displayed in a cell. A major difference between the two languages is that in MDX essentially a single addressing task arises, while in DAX there is an additional aggregation task.

In MDX, learning the tuple syntax was such a big initial hurdle that it fundamentally prevented access to the language for many. Due to the tupel notation, MDX seems too mathematical to many users and the step from the business case, defined in human language, to the implementation in mathematical tuple syntax is long. In DAX, users may think that they are already familiar with the objects of the DAX language - tables, columns and relationships - and therefore assume that it´s easier to transform the business case into the DAX language.

Two typical stumbling blocks in DAX are the terms table and column. Both cube products - Analysis Services MultiDimensional on the one hand and Analysis Services Tabular or Power BI on the other - have in common that they use tables as source objects and derive column objects from them. The transformation process results in a cube. But a table presented by the tabular cube is not the same object as the table where the source data was extracted!

In both cubes - tabular and multidimensional - the Vertipaq engine transforms data from a relational world into data from a dimensional world. Both worlds have their own types, concepts, languages, etc. This fact leads to complexity: the transformation process is complex, the data is complex, the terms are complex, etc.

Due to the philosophy of SelfService complexity is hidden from the user as much as possible. However, the tabular source objects and their properties cannot be completely hidden, since the self-service user must describe in his measure how cube data is aggregated from the relational fact table data. In this way, the power user is confronted with two worlds again and the result is an inevitable object and concept mishmash, which leads to complexity. The language DAX itself does not seem to be complex, especially due to the object types table, column, relationship etc.

In MDX and DAX, a measure is a kind of code snippet. A measure is displayed in the ui like a table column and can be placed by the users interaction anywhere on the report surface. The location where the measure is placed creates a context for the measure. The context provides data that is used for code snippet parameterization. The different contexts result in different parameterizations for the same measure and end in different values.

The following circumstances create changing contexts:

Anticipating the possible contexts and its impact on a KPI is the particular challenge of DAX coding. With MDX, the problem is basically also present, but it is solved for the most part automatically by the software when the cube is developed. When developing KPIs with DAX, the possible contexts must be taken into account. This task can only be solved if the DAX developer understands the mechanics of DAX exactly. But due to the self-service concept, the complex behaviors are mostly hidden in automatisms. The consequences only show up after the measure has already been deployed.