If you are an advanced Excel user already familiar with PivotTables, eventually you will surpass regular features Excel has to offer, and you will start using PowerPivot. Why? Because in today’s world it is all about Big data. There are plenty of sources available for analysis, the amount of data is bigger than ever and increasing each day with astonishing speeds.
Regular Excel couldn’t keep track with data expansion, so they invented PowerPivot, which is able to process a crazy amount of data with ease. To reach the requested speed&scale, DAX (formula language behind PowerPivot) needed different syntax compared to Excel formulas.
To understand the DAX syntax, you first need to understand its two main concepts, which are Filter&Row context. There are many articles about these concepts already available online, but they all go way into technical stuff, making it hard for a new reader to really understand them in simple terms. I will try to keep technical parts to a minimum and provide you with the basic understanding after which it will be much easier for you to understand those concepts on a deeper level.
The filter context
Unlike in Excel formula, DAX doesn’t understand cells concept. In DAX you cannot address cells “C5:C9”. Instead you must address the whole column, then use filters applied to that column to isolate a subset of rows in the table on which you want to compute formula. In order to isolate those rows of data, you will be using filter context.
What is filter context? Basically, it represents the rows in a table that are left after you apply all the filters coming from PivotTable/Slicer/Graph etc… Below is a simple explanation using a well known PivotTable.
For each field in the PivotTable, the first step of calculation is the filtering part. So for Phobos brand, PivotTable filters the whole table on the left side of the picture and leaves only rows which apply to condition brand=Phobos. In second step those rows are left alone and calculation is applied to them. In this case, the calculation is to sum all the Sales amount values in the filtered table. The sum formula doesn’t care how many rows are in the remaining table, it will simply sum all that is left in Sales amount column after filtering. This feature is what makes PivotTables so fast and reliable. After the Sum formula does its part, a computed value is sent to PivotTable in the appropriate field.
This is a simple example with only one filter coming from PivotTable row, but there could be many more filters coming from PivotTable column headers, nested row header filters, slicers etc.
What is important to remember is that no matter how many filters are applied to a table, the process of calculation remains the same.
- Apply all the filters to the Table.
- Isolate rows that match all the criteria.
- Apply the calculation to the remaining rows.
A table that is left after the second step is understood as Filter Context and it is one of two main concepts of DAX.
If you ever thought that GrandTotal and Subtotal fields in the PivotTable are simply summed values of the fields above them, like most of the people, you’ve been deceived. For each field in the PivotTable same calculation steps apply. GrandTotal is not the sum of all the field values in the PivotTable, yet it is a sum of values in a selected column of the table with no filters applied. For simple calculation like Sum it really doesn’t matter since it shows the same figure. But for advanced calculations, it is quite often that you receive different figures at Subtotal and GrandTotal level which does not add up. You might think you did something wrong, but if you remember these 3 calculation steps you will find it much easier to test your calculation and confirm if it is providing the correct result on all required granularity levels.
The row context
Filter context is extremely powerful but has some flaws. To address those flaws in more details lets take a look at the next example. Based on the values in the table above, we would like to calculate what is the Sales value of those products/brands. In Excel, we would do this by multiplying sales amount with the price for each row in a table.
But if we recall Filter context we will notice that it doesn’t understand row dimension, therefore we cannot use this concept to produce the same result using DAX language. We need a concept which understands that it has to iterate a table row by row, doing the same calculation for each row separately. For this situation, we need to use the second most important concept of DAX, which is the Row context.
To use this concept, you have to invoke it somehow using special DAX formulas called iterators (if you create calculated column you automatically invoke row context inside that column). Iterating formulas are easy to spot in DAX. Almost all of them end with “X”. The most important ones are SUMX, AVERAGEX, COUNTX, and FILTER.
To reproduce SalesValue calculation using DAX, we need the following Measure.
SalesValue: =SUMX(Table1,
Table1[Price]*Table1[Sales amount]
)
To make the iterating function valid, you first need to provide a table on which you will do the iteration (the first row in the formula above). Table1[Price]*Table1[Sales amount] (the second row in the formula above) is the formula that will be applied to each row in the table.
If we are to recreate previous situation using DAX, this is what is happening under the PowerPivot hub.
First 2 steps are the same and they are coming from Filter context concept. In step 3 we introduce Row context through our SUMX formula. So in step 3, after all the filters are applied to table, we start to iterate through each of the remaining rows and apply [Price]*[SalesAmount] calculation. It’s like we virtually add additional column (colored red) and in that column we store intermediate values. After all the values are computed row by row, SUM part of SUMX formula kicks in and sums all the values in the virtual column. In step 4 the computed value is sent to the appropriate field in a PivotTable.
Without Row context, it would be impossible to introduce row by row calculations which are an important part of DAX. It is also important to remember that, although we are iterating through rows, DAX doesn’t have a clear understanding which row is currently being processed, so you cannot tell him to process just rows from 5-10 (like you could in regular Excel formulas). Instead, you need to use a combination of FILTER and other table functions to tell DAX more specificly on which table subset it should calculate values.
Portable measures
These two concepts allow each measure to be used across the whole data model. So under different environments (different Row&Filter contexts, which is often referred as Evaluation context) measure will produce (most of the time 😊) wanted results. It doesn’t matter if you put a brand on rows or columns, or if you put a filter on year, month or day, the measure will always compute value just under different Filter Context.
Wrap Up
Before you start to create more advanced measures and models using DAX, you need to get acquainted with Filter&Row context. These topics can be a bit vague for newcomers but are essential for the understanding of CALCULATE (CALCULATETABLE) function, which is the most important formula in DAX and the only one which is able to modify/change the filter context.
These two concepts work together and form Evaluation context, which allows measures to produce desired results under different environments (different filters applied to tables).
If you have any questions, please comment below, and if you enjoyed reading this post, please like/share!
