Book Review – Power Pivot and Power BI

Since Power BI is all the rage right now, I was in need of some material to spice up my knowledge of DAX (read: finally understand how CALCULATE works). I’ve been following the website powerpivotpro.com for quite some time now and they always have very insightful articles about DAX and real world scenarios. Anyway, Rob Collie and Avi Singh, two authors of said site, published their second edition of the book Power Pivot and Power BI – The Excel User’s Guide to DAX, Power Query, Power BI & Power Pivot in Excel 2010-2016. Quite the mouthful. The book is actually the #1 best-selling book in the Excel category at Amazon, so I thought it must be pretty good, right? So I ordered it and read through the whole book. As usual, I give you my personal opinion in the form of a book review.

TL;DR This is the best book about DAX there is out there. Or at least the best book about DAX that I have ever read. Since DAX is the backbone of Power BI and that will not change any time soon, this book is great investment for years to come. Buy.It.Now.And.Read.It.Front.To.Cover. Then read it again.

So let’s start with some stuff that I didn’t like about the book. Keep in mind that this is just nitpicking stuff, because they didn’t pay me enough for a full positive review 😉 Joking aside, there are very few “bad” things to find. First of all, the style of this book is “special”. It looks like it’s been written in Word and it feels like one gigantic blog post. The humor in the book is good, very good, but can get on the nerdy side sometimes. The title of the book mentions Power Query, but there is very little actual content about it. The book is mainly about DAX and the intended audience are Excel pros, so in extension the book is also about Power Pivot. Is that bad? No. But if the title mentions Power Query, one might expect more content about. Instead, they refer you to other books who cover Power Query and the M language. Personally I would have just left Power Query out of the title.

As you can see, the negative aspects of the book are almost non-existent. Let’s start talking about all the good stuff. This book is one of the most thorough books on the DAX language. It starts very easy and slow, so that everyone can pick in. Even as a Business Intelligence professional, there was definitely enough to learn. Throughout the chapters, they get deeper and deeper into the DAX language by slowly adding more layers of complexity. But everything is explained so well – with nice color illustrations – and the basic rules of DAX are pointed out so clearly that I had no trouble mastering the more difficult aspects of DAX. For the first time in years, I finally really understood how row and filter contexts work and how it all comes together with CALCULATE. The authors have done a very good job and the book itself reads very easily. The book consists of a lot of small chapters, so you’re encouraged to read on because it’s all very digestible. Let’s take a look at what we can find in the book:

• The first three chapters are introductory to the world of Power BI and all of its flavors. This is a must-read for people new to the world of Power BI and who are maybe a bit confused with all of those different products and what purpose they serve.
• Chapter 4 is about loading data into Power Pivot. If you have ever used Power Pivot in your life, you can easily skip this chapter.
• With chapter 5, the good stuff starts: calculated columns. In this chapter – but also in the entire book – they are constantly comparing the DAX functionality with how similar it is with Excel functionality. Except it’s a lot better and faster of course. In chapter 6, measures are introduced and they really stress out that it’s the most important thing in Power BI. They really show the benefit of measures and how much time they can save you. They really make some strong use cases on why people just stop using plain Excel and start using Power Pivot / Power BI instead. If you are ever in need of such arguments to favor your case, take a look at those chapters.
• Chapter 6 lays the foundation of the “golden rules” of DAX measures. Once you know how those rules work, you can easily understand how the formula engine calculates everything and you can work yourself through filter contexts.
• In chapter 8, the queen of all functions is introduced: CALCULATE. One of the most important chapters of the book, naturally. Chapter 9 introduces ALL, also know as the “remove a filter” function.
• Up till now, all examples were done on a singe table in Power Pivot. Chapter 10 talks about using multiple tables and their benefits. It also tries to convince people to stop using VLOOKUP 🙂
• So much covered already, but we’re only 80 pages far in the book. At this point, you have already recovered the cost of the book because you are already so much better in DAX. Chapter 11 is an intermission that talks about this.
• Chapter 12 is about disconnected tables, a very interesting and powerful pattern. Chapter 13 builds on the previous chapter and explains FILTER. Chapter 14 dives deeper into time intelligence. There is a nice overview of some of the more interesting time functions. A must-read chapter. Chapter 15 introduces more DAX functions: IF, SWITCH, BLANK and other conditional functions.
• Chapter 16 is also an important chapter, as it talks about all the iterator functions, like SUMX.
• Chapter 17 is more about data modelling and ventures into using multiple data tables (aka having multiple fact tables). Chapter 18 continues with multiple data tables and different granularities.
• Chapter 19 has some nice tips about performance.
• With chapter 20, we finally have the chapter about Power Query. It has a couple of nice examples that show you why Power Query is exactly that awesome. Chapter 21 talks about Power BI Desktop and how you can use it. People who already have a bit of Power BI experience can probably safely skip these two chapters.
• Chapter 22 is about complicated relationships. About multiple relationships between two tables (aka role playing dimensions), but more importantly also about many-to-many relationships. The book really explains well how you can handle these and how you have to set-up your data model. A very small section at the end of the chapter mentions that in Power BI Desktop, you can skip all the fuss and just enable bi-directional filtering on the relationships. But it’s good to know how to handle M2M, if you have to use a version that doesn’t support the bi-directional relationships.
• Chapter 23 is also a golden chapter. It dives deep into row and filter contexts. Probably one of the harder chapters, but definitely worth the read. Chapter 24 fleshes out CALCULATE and FILTER even more. For the DAX lovers 🙂
• Chapter 25 revisits time intelligence and talks about custom calendars. If you’re into that. 🙂 It introduces the GFITW: the greatest formula in the world, so you probably don’t want to miss that.
• Chapter 26 is about advanced calculated columns. Mostly about EARLIER.
• Chapter 27 is a great chapter, as it’s about the new DAX functions introduced with SQL Server 2016. And about DAX variables, which are unbelievably awesome. Another must-read.
• Time for some filler content: chapter 28 is about the advantage of the cloud and PowerBI.com aka Youtube for Excel workbooks. There’s also an addendum talking about SSAS Tabular and an addendum about cube formulas. The book ends with a couple of the most common error messages. Ending on a high note 😉

Conclusion

If you didn’t notice already: I liked this book. A lot. I absolutely recommend it to everyone who wants to learn more about DAX (and it’s definitely a book about DAX, don’t let the title fool you). It doesn’t matter if you’re a seasoned BI professional, an Excel guru or someone just starting with data analysis: you will surely learn something. The book is written in such a way that everything is explained in an easy to understand manner. Complexity is introduced incrementally, chapter after chapter, but it’s all very digestible. The authors know their stuff and they really know how to pass along their knowledge to an audience. A must read.