DAX Formulas for PowerPivot by Rob Collie Holy Macro! Books PO Box 82 Uniontown, OH 44685 DAX Formulas for PowerPivot © 2013 Robert Collie and Tickling Keys, Inc. All rights reserved. No part of this book may be reproduced or transmitted in any form or by any means, electronic or mechanical, including photocopying, recording, or by any information or storage retrieval sys- tem without permission from the publisher. Every effort has been made to make this book as complete and accurate as possible, but no warranty or fitness is implied. The information is provided on an “as is” basis. The authors and the publisher shall have neither liability nor responsibility to any person or entity with re- spect to any loss or damages arising from the information contained in this book. Author: Rob Collie Layout: Tyler Nash Technical Editor: Scott Senkeresty Cover Design: Shannon Mattiza 6’4 Productions & Jocelyn Hellyer Indexing: Nellie J. Liwam Published by: Holy Macro! Books, PO Box 82 Uniontown, OH 44685 USA Distributed by: Independent Publishers Group, Chicago, IL First Printing: November 2012. Updated with corrections June 2013 and February 2014. Printed in USA ISBN: 978-1-61547-015-0 Print, 978-1-61547-212-3 PDF, 978-1-61547-332-8 ePub, 978-1-61547-112-6 Mobi LCCN: 2012949097 ii Table of Contents Acknowledgements ....................................................................................................................................iv Supporting Workbooks and Data Sets........................................................................................................vi A Note on Hyperlinks .................................................................................................................................vi Introduction ..............................................................................................................................................vii 1- A Revolution Built on YOU ......................................................................................................................1 2- What Version of PowerPivot Should You Use? ........................................................................................7 3- Learning PowerPivot “The Excel Way” ....................................................................................................9 4- Loading Data Into PowerPivot ...............................................................................................................13 5- Intro to Calculated Columns .................................................................................................................23 6- Introduction to DAX Measures ..............................................................................................................29 7- The “Golden Rules” of DAX Measures ..................................................................................................49 8- CALCULATE() – Your New Favorite Function..........................................................................................59 9- ALL() – The “Remove a Filter” Function ................................................................................................69 10- Thinking in Multiple Tables .................................................................................................................75 11- “Intermission” – Taking Stock of Your New Powers ............................................................................87 12- Disconnected Tables ...........................................................................................................................89 13- Introducing the FILTER() Function, and Disconnected Tables Continued ............................................99 14- Introduction to Time Intelligence .....................................................................................................111 15- IF(), SWITCH(), BLANK(), and Other Conditional Fun ........................................................................135 16- SUMX() and Other X (“Iterator”) Functions ......................................................................................143 17- Multiple Data Tables .........................................................................................................................151 18- Time Intelligence with Custom Calendars: Advanced Use of FILTER() .............................................161 19- Performance: How to keep things running fast. ..............................................................................185 20- Advanced Calculated Columns ..........................................................................................................197 21- The Final Transformation: One Click That Will Change Your Life Forever ........................................209 A1- Further Proof That the Game is Changing ........................................................................................229 A2- So Much Power, So Little Space: Further Capabilities ......................................................................233 A3- Four Common Error Messages .........................................................................................................235 A4- People: The Most Powerful Feature of PowerPivot .........................................................................236 Index .......................................................................................................................................................240 iii Acknowledgements Bill Jelen – for tremendous support, encouragement, and humor. I never could have navigated the waters of the book trade without your assistance and fair treatment. David Gainer - for teaching me half of everything valuable that I know, and teaching me to trust the other half. Three lifetimes would not be long enough to repay you. WWDD (What Would Dave Do?) – most impactful role-playing game of all time :) Ken Puls – for crystallizing the need for me to write this book. All is right with Nature now – we are back to a state where there is nothing about Excel known to Rob and unknown to Ken. Zeke Koch - for being so “insanely” awake and uncompromising (in a good way), and for letting some of that rub off on me. WWZD was the only other instance of the “WW” game I ever played. The late Heikki Kanerva - for taking a chance on me, supporting me, and advocating for me. You are missed. David Gonzalez - for encouraging me to go talk to Heikki. Jeff Larsson - for helping me survive (barely!) the campaign of 1997-1999. David McKinnis - for the tour of the Word97 Tools Options dialog, "a monument to the spineless backs of program managers everywhere." Ben Chamberlain, Malcolm Haar, and Chetan Parulekar - for helping me understand that I was actually helpful (cue the Sally Field acceptance speech) and helping an insecure guy find his first footing. John Delo - for patching OLE32 in RAM, the single greatest "stick save" in the history of software. Also for being a worthy adversary, and for taking the fountain dunking like a man. (The champagne squirtgun in the eyes was a crafty defense, well played). Jon Sigler - for being next in line sticking his neck out for me. Richard McAniff - for ovens and steaks, and more wisdom than I appreciated at the time. Robert Hawking and Juha Niemisto - for patiently welcoming yet another green program manager to the complexities of your world. Amir Netz - for sending me that “you should come look at our new project” email in 2006, and for encouraging me to start the blog in 2009. David Kruglov - for reinforcing what Amir said, and for getting me into that SharePoint conference. Maurice Prather – for introducing me to David K, for bailing us out big time as we were leaving town, and generally just being a great friend. I still owe you a long-overdue explanation for a few things. Donald Farmer, TK Anand, Ariel Netz, Tom Casey, John Hancock – for supporting me in a VERY difficult time, and for giving me a precious eight-month window during which I found my new place in the world. Donald “Tommy Chong” Farmer, again – for being such an amazingly good sport and good human throughout, even after switching teams. Kasper de Jonge - for incredible transcontinental assistance and kinship, for saying nice things about my hoops game after trouncing me, for moving to the US and taking over the Rob Collie chair at MS (!), for reviewing the book, and for providing some much-needed screenshots there at the end. Denny Lee – for critical support on occasions too numerous to list. Quite simply the man, eh? Marianne Soinski - for teaching a certain 12 year old underachiever how to write, to REALLY write, and for forgiving (in advance!) the writing sins I would later commit in these pages and on the blog. The Sambreel Crew – mas tequila por favor. Lee Graber – wow, we’ve come a LONG way since sitting at that conference table staring at each other in confusion. iv Howie Dickerman, Marius Dumitru, and Jeffrey Wang – for fielding my questions over the years, even (especially!) when they were user error. Howie Dickerman, again – for also reviewing the book, on a short deadline. Marco Russo and Alberto Ferrari, aka “The Italians” – for providing that next level of teaching, at and beyond the fron- tiers of my comprehension. David Churchward, Colin Banfield, and David Hager – the all-stars of guest blogging. You are all too modest to admit the extent of your own skill and contribution. Dany Hoter and Danny Khen – for a truly pragmatic, open-minded, and humble frame of mind. For seeking input in a world where everyone’s walkie-talkie is stuck on SEND. It really stands out. Eran Megiddo – for retroactively helping me to digest some of life’s starkest truths. Chad Rothschiller, Eric Vigesaa, Allan Folting, Joe Chirilov – I smile every time I think of you guys. Friendly, smart, witty monsters of the software trade. You all helped me more than I helped you. Mike Nichols – Mexico. Greg Harrelson – for starting that fantasy football league in 1996, inadvertently leading to my Excel obsession. Joe Bryant – for writing the trendsetting article “Value Based Drafting,” which really, REALLY spun me into full Excel addiction. Dennis Wallentin - for excellent gang signs, for being a great human, and for fighting through. Dick Moffat - for opening my eyes to the slide in Excel’s credibility as a development platform. Mary Bailey Nail – for weathering the artillery barrage, for forcing me to discover the GFITW, and for guaranteeing that all “year over year” biz logic I encounter in the future will seem like child’s play. Dan Wesson – for welcoming a “spreadsheet on steroids” into the scientific world, and for enjoying it. Also, for intro- ducing the word “anogenital” into my tech talks – the most guaranteed laugh generator of all time. Jeff “Dr. Synthetic” Wilson – for your determination and feedback. Scott Senkeresty – for sticking around through many distinct phases of Rob over the past sixteen-plus years, and for reviewing this book more carefully and enthusiastically than I could have ever expected of anyone (in raw form no less!) The rest of the crew at Pivotstream – for having the courage and foresight to bet the farm on PowerPivot three years ago, and for supporting me in this book project. Tyler Nash – for patiently processing endless rounds of revisions. Pandora - no one's jazz is smoother than yours. The crew at Cedar-Fairmount Starbucks - for a steady supply of caffeine and social interaction over the past few years. Phoenix Coffee - for inventing the Stuporball. You were the coffee mistress of bookwriting – please do not tell Star- bucks. RJ and Gabby Collie – for being proud of your dad. I never would have guessed how cool that would feel. Also, for being such thoughtful young people in general. Jocelyn Collie – for sticking close during the move, for accepting and appreciating your goofball husband “as-is,” for inspiring my switch from defense to offense, for school mornings, and for always knowing where everything is. v Supporting Workbooks and Data Sets When I first committed to write the book, I decided that I would not attempt a companion CD or similar electronic companion of samples, data sets, etc. I made that decision for two reasons: 1. I’ve found that when I am able to say something like “take a look at the supporting files if this isn’t clear,” that provides me too easy of an escape hatch. Treating the book as a purely standalone deliverable keeps me disciplined (or more disciplined at least) about providing clear and complete explanations. 2. Companion materials like that would have delayed release of the book and made it more expensive. But as I neared completion of the book I realized that I could still provide a few such materials on an informal basis, downloadable from the blog. So I will upload the original Access database that I used as a data source, as well as the workbook itself from various points in time as I progressed through the book: http://ppvt.pro/BookFiles Note that this will be a “living” page – a place where you can ask for clarification on the files, suggest im- provements to them, etc. As time allows I will modify and improve the contents of the page. A Note on Hyperlinks You will notice that all of the hyperlinks in this book look like this: http://ppvt.pro/<foo> Where <foo> is something that is short and easy to type. Example: http://ppvt.pro/1stBlog This is a “short link” and is intended to make life much easier for readers of the print edition. That link above will take you to the first blog post I ever published, which went live in October of 2009. Its “real” URL is this: http://www.powerpivotpro.com/2009/10/hello-everybody/ Which would you rather type? So just a few notes: 1. These short links will always start with http://ppvt.pro/ – which is short for “PowerPivotPro,” the name of my blog. 2. These links are case-sensitive! If the link in the book ends in “1stBlog” like above, typing “1stblog” or “1stBLOG” will not take you to the intended page! 3. Not all of these links will lead to my blog – some will take you to Microsoft sites for instance. 4. The book does not rely on you following the links – the topics covered in this book are intended to be complete in and of themselves. The links provided are strictly optional “more info” type of content. vi Introduction My Two Goals for This Book Fundamentally of course, this book is intended to train you on PowerPivot. It captures the techniques I’ve learned from three years of teaching PowerPivot (in person and on my blog), as well as applying it extensive- ly in my everyday work. Unsurprisingly, then, the contents herein are very much instructional – a “how to” book if ever there was one. But I also want you to understand how to maximize PowerPivot’s impact on your career. It isn’t just a bet- ter way to do PivotTables. It isn’t just a way to reduce manual effort. It’s not just a better formula engine. Even though I worked on the first version of PowerPivot while at Microsoft, I had no idea how impactful it would be until about two years after I left the company. I had to experience it in the real world to see its full potential, and even then it took some time to overwhelm my skeptical nature (my Twitter profile now describes me as “skeptic turned High Priest.”) This is the rare technology that can (and will) fundamentally change the lives of millions of people – it has more in common with the invention of the PC than with the invention of, say, the VCR. The PC might be a particularly relevant example actually. At a prestigious Seattle high school in the early 1970’s, Bill Gates and Paul Allen discovered a mutual love for programming, but there was no widespread demand for programmers at that point. Only when the first PC (the Altair) was introduced was there an op- portunity to properly monetize their skills. Short version: they founded Microsoft and became billionaires. But zoom out and you’ll see much more. Thousands of people became millionaires at Microsoft alone (sadly, yours truly missed that boat by a few years). Further, without the Altair, there would have been no IBM PC, no Apple, no Mac, no Steve Jobs. No iPod, no iPhone, no Appstore. No Electronic Arts, no Myst. No World of Warcraft. The number of people who became wealthy as a result of the PC absolutely dwarfs the num- ber of people who had anything to do with inventing the PC itself! I think PowerPivot offers the same potential wealth-generation effect to Excel users as the PC offered bud- ding programmers like Gates and Allen: your innate skills remain the same but their value becomes many times greater. Before diving into the instructional stuff in Chapters 2 and beyond, Chapter 1 will summarize your exciting new role in the changing world. And like many things in my life, the story starts with a movie reference  vii viii DAX Formulas for PowerPivot: A Simple Guide to the Excel Revolution 1- A Revolution Built on YOU Does This Sound Familiar? In the movie Fight Club, Edward Norton’s character refers to the people he meets on airplanes as “single serving friends” – people he befriends for three hours and never sees again. I have a unique perspective on this phenomenon, thanks to a real-world example that is relevant to this book. A woman takes her seat for a cross-country business flight and is pleased to see that her seatmate appears to be a reasonably normal fellow. They strike up a friendly conversation, and when he asks her what she does for a living, she gives the usual reply: “I’m a marketing analyst.” That answer satisfies 99% of her single-serving friends, at which point the conversation typically turns to something else. However, this guy is the exception, and asks the dreaded follow-up question: “Oh, neat! What does that mean, actually?” She sighs, ever so slightly, because the honest answer to that question always bores people to death. Worse than that actually: it often makes the single-serving friend recoil a bit, and express a sentiment bordering on pity. But she’s a factual sort of person, so she gives a factual answer: “well, basically I work with Excel all day, making PivotTables.” She fully expects this to be a setback in the conversation, a point on which she and her seatmate share no common ground. Does this woman’s story sound familiar? Do you occasionally find yourself in the same position? Well imagine her surprise when this particular single-serving friend actually becomes excited after hearing her answer! He lights up – it’s the highlight of his day to meet her. Because, you see, on this flight, she sat down next to me. And I have some exciting news for people like her, which probably includes you :-) Excel Pros: The World is Changing in Your Favor If you are reading this, I can say confidently that the world is on the verge of an incredible discovery: it is about to realize how immensely valuable you are. In large part, this book is aimed at helping you reap the full rewards available to you during this revolution. That probably sounds pretty appealing, but why am I so comfortable making bold pronouncements about someone I have never met? Well, this is where the single-serving friend thing comes in: I have met many people like you over the years, and to me, you are very much ‘my people.’ In fact, for many years while I worked at Microsoft, it was my job to meet people like you. I was an engineer on the Excel team, and I led a lot of the efforts to design new functionality for relatively advanced users. Meeting those people, and watching them work, was crucial, so I traveled to find them. When I was looking for people to meet, the only criteria I applied was this: you had to use Excel for ten or more hours per week. I found people like that (like you!) all over the world, in places ranging from massive banks in Europe to the back rooms of automobile dealerships in Portland, Oregon. There are also many of you working at Microsoft itself, working in various finance, accounting, and marketing roles, and I spent a lot of time with them as well (more on this later). Over those years, I formed a ‘profile’ of these ‘ten hour’ spreadsheet people I met. Again, see if this sounds familiar. Attributes of an Excel Pro: • They grab data from one or more sources. • They prep the data, often using VLOOKUP. • They then create pivots over the prepared data. • Sometimes they subsequently index into the resulting pivots, using formulas, to produce polished reports. Other times, the pivots themselves serve as the reports. • They then share the reports with their colleagues, typically via email or by saving to a network drive. 1