DAX Formulas for PowerPivot
The Excel Pro's Guide to Mastering DAX by Rob Collie
A Publication from Holy Macro! Books
This is the book that opened my eyes about how PowerPivot transforms Excel into much more than a spreadsheet – and I’m supposed to know something about spreadsheets. It’s a fun read and it will teach you skills that you really need to know, if you’re crunching significant amounts of data in Excel.
Click here for Spanish Edition.
This Book is written from a traditional “Business Intelligence” standpoint and is aimed at the audiences of BI pros, SharePoint pros, and Network Administrators
PowerPivot is the Best Thing to Happen to Excel in 20 Years. This book unlocks its secrets for today's Excel users.
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.
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 extensively 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 better 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.”)
Table of Contents
- Acknowledgements v
- A Note on Hyperlinks vii
- Introduction ix
- Chapter 1- A Revolution Built on YOU 1
- Chapter 2- What Version of PowerPivot Should You Use? 7
- Chapter 3- Learning PowerPivot “The Excel Way” 9
- Chapter 4- Loading Data Into PowerPivot 13
- Chapter 5- Intro to Calculated Columns 23
- Chapter 6- Introduction to DAX Measures 29
- Chapter 7- The “Golden Rules” of DAX Measures 49
- Chapter 8- CALCULATE() – Your New Favorite Function 59
- Chapter 9- ALL() – The “Remove a Filter” Function 69
- Chapter 10- Thinking in Multiple Tables 75
- Chapter 11- “Intermission” – Taking Stock of Your New Powers 87
- Chapter 12- Disconnected Tables 89
- Chapter 13- Introducing the FILTER() Function, and Disconnected Tables Continued.. 99
- Chapter 14- Introduction to Time Intelligence 111
- Chapter 15- IF(), SWITCH(), BLANK(), and Other Conditional Fun 135
- Chapter 16- SUMX() and Other X (“Iterator”) Functions 143
- Chapter 17- Multiple Data Tables 151
- Chapter 18- Time Intelligence with Custom Calendars: Advanced Use of FILTER() 161
- Chapter 19- Performance: How to keep things running fast 185
- Chapter 20- Advanced Calculated Columns 97
- Chapter 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- Supporting Workbooks and Data Sets 237
- A5- People: The Most Powerful Feature of PowerPivot 238
About The Author
A former Microsoft engineering leader, Rob Collie is an entrepreneur, author, and consultant who believes Excel is enjoying a renaissance in power and importance - a trend that provides massive opportunities for the millions of Excel practioners worldwide. His passion is empowering and the Excel Professional to seize those opportunities. When he's not training, consulting, or writing, Rob can be found in his laboratory, devising new tools and techniques for the Excel community. He also operates the world's leading PowerPivot website (PowerPivotPro.com) and occassionally sleeps.
Click here for Spanish Edition.