Sunday, December 16, 2012

DAX Groupers: SUMMARIZE and AGGX(VALUES())

Groupers are your friends
Sometimes you need to perform what I'd call a multi-pass aggregation.  Rolling up data to a higher level of granularity and then performing additional calculations on it.  Basically, the need to achieve the equivalent of SQL's GROUP BY in DAX, and then apply some additional calcs. 

Luckily DAX has a few Groupers that can handle this quite nicely.

Saturday, December 15, 2012

A Time dimension specifically for PowerPivot & AS Tabular

I need a time dimension BOYEE!!
The other day I needed to build a model that would enable slicing of events by time of day.  My users wanted to be able to easily focus in on events that happened just in the morning or just after noon.  Several different slices or bands of time.

So obviously, since I'm a big proponent of dimensional models, and since my users naturally think dimensionally when they analyze and explore data, I knew that I needed a big clock in my model... 

Make that a time dimension.  But not just any old time dimension.  I needed one with flavor...