One of the most important things you need to know when you work with PowerPivot is Pivottable context. Although this is not so obvious, understanding this helps you in writing DAX functions.
When i was at teched 2010 I attended Ashvini Sharma’s presentation on DAX (which you can see here for free) he had a great way of explaining the context by using dax formula’s. Today i want to show you what PivotTable context is all about using Ashvini’s explanation.
We start with a pivottable with 2 values on both axes, CalendarYear on X and Color on Y:
So what happens on each cell? When we put a measure in there it will restrict the value to the measure where color is the current row and year is the current column. So the first cell would give a value of the measure where the color = Azure and the year = 2005. It makes an aggregation of the data based on these values.
To show you what happens is create a measure that shows the current filter context, we can also show text inside the pivottable:
As you can see we have for each cell a different context, also on the total columns and rows we have multiple values as context.
I used the following DAX formula to show you the context:
=CONCATENATE(
if(COUNTROWS(VALUES(DimDate[CalendarYear])) =1," "&VALUES(DimDate[CalendarYear]) & " "," Multiple years"),
if(COUNTROWS(VALUES(DimProduct[ColorName])) =1,""&VALUES(DimProduct[ColorName]),"Multiple colors"))What we do here is get the value from the current DimDate[CalendarYear] and DimProduct[ColorName] contect using the Values function.
When we put values in it we see that the Pivottable will automatically calculate the sum of daysinstock :
So why is this important ? In many scenario’s we not only want to see the current context but also see the value from the current context against a value from another context. A lot of DAX functions are build to let you change the current context. A few examples:
- Compare the daysinstock in context to the daysinstock of all colors.
=CALCULATE(sum(FactInventory[DaysInStock]), all(DimProduct[ColorName]))
Using the calculate function you can override the filtercontext, we now tell the calculation engine we have the sum for all colors in the context of the current year (we don’t override this one) - Change the current context by using time intelligent function, we want to compare the current year to previous year.
=CALCULATE(sum(FactInventory[DaysInStock]), PREVIOUSYEAR(DimDate[Datekey]))
Again using the calculate function we use the value of DimDate[Datekey] which is in context to subtract one year. So in this case we move the context to one year ago compared to the current contex
As you can see we need context in a a lot of situations, even when you don’t know it. Filter context is a thing that is almost automatically understood without thinking of it, but in some cases, mostly when working with DAX, you need to think more deeply about filter context.