# Thinking in DAX: Non-linear Cognition at Play

December 3, 2012 1 Comment

Over the last year, I have had the opportunity to teach PowerPivot and DAX to a good number of technical and non-technical people in a variety of industries. After each training, I recount what went right and what went wrong, and I have identified some clues that offer some insight on the way people assimilate this technology and ultimately decide whether or not to link themselves to it.

Perhaps a chart can help explain this better. Let’s step aside from **DAX** for a moment, and think about a more traditional subject area which is typically taught in school: **World History**.

When you are learning World History, each milestone builds on the prior one and there is an incremental process which can be graphed as a curve:

At any give point in the curve you are one step ahead than the prior one. This, of course, with the exception of very last phase when you have arrived to a theoretical moment of complete assimilation of all knowledge.

If you are learning, for example, about the European Union it is likely you will be building on earlier topics related to World War II. As you follow the chain of topics, you can realistically stop in any of them without harm.

DAX ** does not **follow this learning pattern. First of all, even for the some of the most basic Time Intelligence functions like “Running Total since Inception”, there is a need to understand more advanced concepts of filter context. Due to this, the pattern can instead be charted as the following:

DAX can appear simple (and easy) at first due to its similarities with Excel functions. Fully additive measures can be created quickly. In some cases, PowerPivot even creates them automatically (such is the case of implicit measures). And you may quickly hit the sudden understanding that DAX generates **portable calculations** that can be sliced and diced by other data attributes.

At that point, an analyst using PowerPivot may feel empowered and attempt to generate something a little more complex. Something, for example, like a “Year over Year Growth” ratio. As there are no built-in functions to get the answer directly, there is a need to understand how to build your own; for first time users, though, the most likely experience is that one getting stuck and experiencing a bit of frustration.

And here lies the power – and the current weakness – of DAX based tools: they are – for the most part – a blank canvas. To develop some of the advanced, non-additive analytical measures you will want to use, there is a need to understand quite a bit of how the engine interprets incoming computations. **But for those that take the time and interest to learn how the language actually works, doors open to a huge analytical arsenal made up of DAX functions and patterns that can be used to develop virtually any kind of calculation needed.**

The flat horizontal lines on my “**DAX Learning Pattern**” chart happens when the person is cognitively stuck in the ** “I don’t get it! I just don’t see it!”** phase. During that time, they may feel no knowledge is being acquired. They may be working on a DAX problem for a long time with no feeling of being close to a solution.

The best way I can explain this is through a Gestalt example:

What do you see? An old woman with a big nose? Or a young woman turning to the side?

Either way, once you see the first one it will take a bit of work – and concentration – to identify the other one. You may stay looking at the picture for some time but when you finally see it, it will be so obvious you can’t help but seeing it it now.

In DAX, this can happen when learning about the many powers of the CALCULATE function, or when fully assimilating the fact filter context propagate in one direction by default, or even when understanding how to change the granularity of a calculation using an AggX function.

In essence, what is required here is non-linear thinking: rather than straight step by step logic, what advances our level of DAX is a sequence of cognitive reframing scenarios in which the solution is assimilated based on sudden understanding – many times associated with a loud ‘AHA!’ -, rather than through paced and gradual knowledge acquisition.

Also note that not all horizontal/vertical lines in the DAX learning pattern chart have the same size. This is because after a deep reframing that leads to better understanding, there may be a small gain in direct formula applications. In other cases, a less dramatic reframing can have huge impact in formula applications. Or vice versa. In a similar way, you may be stuck for a short or long time, when trying to grasp how to get a formula to work, or when analyzing a how a working formula actually computes.

Let’s see an example. Many of you are already familiar with DAX but for the sake of this example, imagine you are learning about the most relevant functions and you hear their definition for the first time. In that scenario, lets define a few of them:

AVERAGEX/SUMX – Takes a table and iterates through each row calculating an expression. As a final pass, all row expression output values are averaged or added (depending on the function selected).

VALUES – Returns a one table column removing duplicates over the column that has been used as a parameter.

That is clear and relatively simple. When testing those functions (in this case, using AdventureWorksDW), we get the Total Product Cost by using:

## [Total Product Cost]:=SUMX(

## FactResellerSales,

## FactResellerSales[OrderQuantity] *

## FactResellerSales[ProductStandardCost]

## )

Also, I could count the products with sales on any given month with the next expression:

## [Count of Products With Sales]:=COUNTROWS(

## VALUES( FactResellerSales[ProductKey] )

## )

In the fact table, we have many sales for any given product. By using VALUES we get the list of unique products with sales. At this point, the implementation of those functions directly reflects the definition given above and the calculation output is straight forward.

Let say somebody asks now to generate a [Total Product Cost] calculation in which, at the **monthly level** is the default value (** sum**) but at the

**yearly level**it should be

*.*

**the average of all monthly values**For people learning DAX, this could quickly translate into the flat line on the DAX learning pattern chart. Interestingly, after the example given above – __ they already know all they need to know in order to get this done.__ But it takes some time for the idea to really ‘click’.

Here is the calculation:

## AVERAGEX(

## VALUES( DimDate[EnglishMonthName] ),

## [Total Product Cost]

## )

If you are new to this type of calculation, you may experience the same thing as when you where looking at the old lady picture above, and focusing hard trying to find the young lady in the very same outline. You may stay there for a while, but then it finally happens: AHA!. If you create a list of unique month values at the year level, you can take each individual output – which we arrived at by summing – and then average at that level. However, at the month level, the average will divide by 1 (the number of unique values at that level), so we keep the output SUM value.

Once the sudden re-framing and *aha!* moment occurs, you can’t stop seeing the pattern every time you look at the calculation.

In the end, one calculation can be interpreted by the xVelocity engine in multiple different ways, even without a single change on the expression. This is because the interaction of query and filter context can generate different output values using data across many tables at multiple granularities.

Some people seem to be pre-wired for this type of thought process. To others, this doesn’t come very naturally but can definitely be achieved if there is persistence (that is a big if, considering many people in the business world have much more to do than learning a new tool/technology).

What I have noticed is that non-technical people that end up achieving some level of mastery of DAX is because at some point they are able to shift their attention away from the ‘immediate satisfaction’ of solving the problem at hand and dedicate a few extra minutes to experiment how functions behave under different conditions. In other words, they become interested in the language itself, not just as an intermediary vehicle.

Not every analyst in the business world or technical person assigned BI responsibilities will have the time and interest to make this shift. Interestingly, however, some of the people I have seen that most closely relate to this experimentation attitude are some of the subject matter experts that in many cases end up providing QA input for data warehousing projects. In that sense, I believe the conditions for this technology to flourish are there even when it requires a non-linear thought process that may be foreign to some business users.

Interesting notes – it seems that a good excercise during a course could be a sort of “game” that raise the interest to the language itself in order to reach an immediate goal (winning a prize, solving a puzzle…) so to involve the more reluctant people.