One of the most important things you need to know about PowerPivot and DAX is row and column context. Creating measures become a lot easier once you understand it.
Let me give you an example of a problem that is not so hard once you understand row and column context. I had this question today on my blog:
I am trying to calculate Months Exposure for each model, i.e. how many months since the first Distributed Units
So if a product was first distributed on January 2009, MonthsExposure would be 24, etc.
and then he explained to me how he did this using excel:
This is how I calculate MonthsExposure in Excel:
MinDate ={MIN(IF(Model=”Nokia N8″,DateKey)}
MonthsExposure =DATEDIF(MinDate,TODAY(),”M”)
In PowerPivot this scenario is really easy to solve using the row and column context. I have created a previous blog post about what the Context is with PowerPivot you can check it out here: http://www.kasperonbi.com/what-is-that-powerpivot-pivottable-context-they-keep-talking-about
In short: when you work with a Pivottable and you put values on the x and or y ax this will mean the value that you put in the Values part automatically uses the value on that row and column to filter by. An example is this:
I put the sum of Onorderquantity in the Values list, it shows me the total sum, row and column context here is that there is no filter on each row, everything from the table is summed. Now let’s put Country on the Y ax:
Now what happens ? Each row is automatically filtered by the country that you see in the Row. Each row contains the sum of all the values of the field onorderquantity in the fact table for that specific country. Now what happens if you put year on the x ax:
Let’s take a look a what this means for the value in the red circled cell. This cell contains the sum of all the values of the field onorderquantity in the fact table for that specific country (China) for the year (2008). This means this is not just one value but is a set of values that is summed up to get one value. The measure makes sure we summarize it.
Back to the example of the question from my blog. He wanted to have the first date a specific product was sold. Remember that by putting the Products on rows we create a filter of all the rows by that product for the measure that we put in the values. The measure must be an aggregation, the filter returns a lot of rows.
In this case we want the first date a product ever was sold, This fact is captured in the field: FactInventory[DateKey].
Creating a measure with this field and putting it as a value in a pivottable that has Products on y ax (rows) will give a list of all the FactInventory[DateKey]’s for this Product. We now have to make sure we use an function that returns only one value. In this case we want the first date. We can use this measure: FIRSTDATE(FactInventory[DateKey])
This returns the first date that a product was sold.
Expanding this measure to determine how many days this was exposed would be this: =ROUND(1. * NOW() – FIRSTDATE(FactInventory[DateKey]),0)
I used the 1. * to calculate the difference in days read this blog post for more info: “How can I calculate difference between two dates in DAX (seconds, minutes, hours, days and months)”
I hope this blog post got you thinking about Row and filter context in PowerPivot. It really simplifies building measures if you understand it.
For more info about row and column context (and general DAX) you can watch this great webcast from Ashvini Sharma from the SSAS PM team :
Enriching Microsoft PowerPivot for Microsoft Excel Applications Using DAX (Data Analysis Expressions)
DAX (Data Analysis Expressions) is a new expression language for end users to add business logic to their PowerPivot applications. DAX offers a lot of power and flexibility while retaining the the simplicity and familiarity of Excel like formulas. This session explains the fundamentals and concepts behind DAX and demonstrates how to add business logic in PowerPivot using calculated columns and measures. This session also introduces you to various categories of DAX functions — scalar functions, table functions, aggregates, time intelligence — but is not intended to be a comprehensive overview.
View it here