Collie - DAX formulas for PowerPivot: the Excel pros guide to mastering DAX

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 system 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 respect 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 64 Productions & Jocelyn Hellyer

Indexing: Nellie J. Liwam

Published by: Holy Macro! Books, PO Box 82, Uniontown OH 44685

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

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, weve come a LONG way since sitting at that conference table staring at each other in confusion.

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 frontiers 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 everyones walkie-talkie is stuck on SEND. It really stands out.

Eran Megiddo for retroactively helping me to digest some of lifes 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 Excels 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 childs play.

Dan Wesson for welcoming a spreadsheet on steroids into the scientific world, and for enjoying it. Also, for introducing 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 Starbucks.

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.