Introducing PowerPivot DAX Measures

Up until this point, I’ve just used DAX (PowerPivot’s extension to the Excel formula language) to create calculated columns in the PowerPivot window.

There’s a lot of utility in DAX calc columns, but that’s not where DAX really shines.  DAX measures let you do things in Pivot reports that simply weren’t possible in Excel before.

In the last video, I mentioned that merely showing raw sales amount didn’t really tell me whether my products sold better or worse in varying temperatures, since there are varying numbers of days of each temperature – perhaps the relatively low sales on Hot days is just because there weren’t many Hot days?

Enter DAX measures, a PowerPivot feature on par with relationships, slicers, and mashups.  But you might overlook them on a casual tour through the PowerPivot for Excel addin, since they live behind a single ribbon button.

DO NOT OVERLOOK DAX MEASURES!  There is literally a world of power lurking there.  I want to make absolutely sure that you grasp them – both their power as well as the “how to,” so I will spend the next couple of posts on them.

First, a word about videos vs. text and screenshots

Awhile back I asked for feedback on which format was preferred – text and screenshots, or videos.  Videos got more votes, by about two-to-one.  But my text-and-screenshot posts are getting more views than my video posts.

Clearly, those of us who are too busy to watch videos are also too busy to respond to surveys 🙂

I respect that – personally I think I prefer to consume text and screenshots over videos.  A video seems like a commitment, whereas scanning text is on my terms, even if on net I spend the same amount of time on either.  So here is my new philosophy, always subject to revision:

1. I’m going to use videos whenever I am introducing something new, that benefits from the explanatory power of video
2. Quick tips and tricks, as well as “power” techniques – I think I’m going to lean toward text and screenshots for these, unless it’s an in-depth technique
3. Even when I use video, I’m going to try to summarize the content of the videos – not as an attempt to replicate their content, but more as a table of contents so you know why I think it’s worth your time to watch the video

So, I’ll share the videos first, and a summary after.

The Videos

PowerPivot DAX Measures Pt 1

PowerPivot DAX Measures Pt 2

(These are the fixed versions from 11/24/09)

Videos Summary

1. Quick demo of conditional formatting and number formatting, and how surprisingly impactful they are
2. The DAX Measure Dialog
3. Qty per Day can be calc’d using =SUM([Quantity]) / COUNTROWS(RELATEDTABLE(DimDate))
   1. Simple formula, but how does it work?  Please consult…
4. …The Five Golden (and Simple) Rules of DAX Measures
   1. Home Table = The table where the numeric columns are
   2. Columns always wrapped in aggregation functions like SUM()
   3. Calcs always are working against the source tables, not the pivot report
   4. Two phases:  Filter, then Calculate
   5. Think of things as if they happen cell-by-cell
5. Once you grasp those, you can suddenly do AMAZING things