Power BI is the latest and greatest in business intelligence (BI) software. There are so many great things about this Microsoft product that it is hard to know where to start. Perhaps one of the most important things to note about Power BI is that it is designed with business analysts and Excel users in mind. You do not need to be an IT professional to be able to use this software well.
Power BI has capabilities across four important phases of a business intelligence project:
This book, Supercharge Power BI: Power BI Is Better When You Learn to Write DAX, teaches you the skills you need to use Power Pivot (the modelling tool bundled with Power BI) and the DAX language. Power Pivot brings everything that is good about enterprise-strength BI tools directly to you right inside Power BI Desktop—and without the negative time and cost impacts normally associated with big-scale BI projects. In addition, it is not just the time and money that matter. The fact that you can do everything yourself directly inside Power BI is very empowering. Analyses that you would never have considered viable in the past are now “can do” tasks within the current business cycle.
It is worth pointing out that you can use Power BI without learning Power Pivot. However, Power BI is definitely better when you learn to write DAX. If you don’t invest time in learning DAX and Power Pivot, you will be able to take advantage of only the basic capabilities of the Power BI tool. Imagine being able to use only the SUM() function in Excel. You would be able to produce only very basic and simplistic spreadsheets. Similarly, with Power BI, if you don’t learn the DAX language and how the Power Pivot engine works, you will be limited to simplistic capabilities that restrict the value you can get from the tool.
There is another significant benefit to learning Power Pivot and DAX for Power BI. These skills are fully transferable to Excel. Although in this book you will be learning the DAX language using the Power BI user interface, you will be able to easily move these new skills into Power Pivot for Excel should you want to do that. And who wouldn’t?
Supercharge Excel
Supercharge Power BI: Power BI Is Better When You Learn to Write DAX has been written specifically to teach Power Pivot and DAX using Power BI Desktop. I have written a sister book, Supercharge Excel: When You Learn to Write DAX for Power Pivot. These two books cover the same basic content but with a different user interface. Because the skills you will learn in this book are fully transferable to Power Pivot for Excel and vice versa, you really need only one of these books to secure the required skills. However, if you want to learn about the differences in the UI and practice what you have learnt, then reading Supercharge Excel will certainly help you cement your learning across the different UIs.
Why You Need This Book
I am a full-time Power BI consultant, trainer, and BI practitioner. I have taught many Excel users how to use Power Pivot and Power BI at live training classes, and I have helped countless others online at various Power BI forums. This teaching experience has given me great insight into how Excel users learn Power BI and what resources they need to succeed. Power BI is very learnable, but it is very different to Excel; you definitely need some structured learning if you want to be good at using this tool. I have learnt that Excel users need practice, practice, practice. The book you’re reading right now, Supercharge Power BI: Power BI Is Better When You Learn to Write DAX, is designed to give you practice and to teach you how to write DAX. If you can’t write DAX, you will never be good at Power BI or Power Pivot.
I refer above to Excel users, and that is quite deliberate. I have observed that Excel professionals learn DAX differently than do IT/SQL Server professionals. IT/SQL Server professionals are simply not the same as Excel business users. SQL Server professionals have a solid knowledge of database design and principles, table relationships, how to efficiently aggregate data, etc. And of course there are some Excel users who also have knowledge about those things. But I believe IT/SQL Server professionals can take a much more technical path to learning DAX than most Excel users because they have the technical grounding to build upon. Excel users need a different approach, and this book is written with them in mind. That is not to say that an IT/SQL Server professional would not get any value from this book/approach; it really depends on your learning style. But suffice it to say that if you are an Excel professional who is trying to learn DAX, this book was written with your specific needs in mind.
I am an Excel user from way back—a long way back actually. I’m not the kind of guy who can sit down and read a novel, but I love to buy Excel reference books and read them cover to cover. And I have learnt a lot about Excel over the years by using this approach. When I find some new concept that I love and want to try, most of the time I just remember it. But sometimes I add a sticky note to the page so I can I find it again in the future when I need it. In a way, I am incrementally learning a small number of new skills on top of the large base of skills I already have. When you incrementally learn like this, it is relatively easy to remember the detail of the new thing you just learnt.
It’s a bit like when a new employee starts work at a company. Existing employees only have to learn the name of that one new person. But the new employee has to learn the name of every person in the entire company. It is relatively easy for the existing employees to remember one new name and a lot harder for the new person to start from scratch and learn all the names. Similarly, when you’re an experienced Excel user reading a regular Excel book, you already know a lot and need to learn only a few things that are new—and those new bits are likely to be gold. It is easy to remember those few new things because often they strike a chord with you. Even if you don’t remember the details, the next time you face a similar problem, you’ll remember that you read something about it once, and you’ll be able to go find your book to look it up.
Well, unfortunately for seasoned Excel users, Power BI is a completely different piece of software from Excel. It shares some things in common (such as some common formulas), but many of the really useful concepts are very different and completely new. They are not super-difficult to learn, but indeed you will need to learn from scratch, just as that new employee has to learn everyone’s name. Once you get a critical mass of new Power BI knowledge in your head, you will be off and running. At that point, you will be able to incrementally learn all you want, but until then, you need to read, learn, and, most importantly, practice, practice, practice.
I think about learning as being either passive or active. An example of passive learning is lying in bed, reading your Power BI book, nodding your head to indicate that you understand what is being covered. When you learn something completely new, you simply can’t take this approach. I read a lot of Power Pivot books early in my discovery, but the first time I sat in front of my computer and wanted to write some DAX, I was totally lost. What I really needed to do was change from a passive learning approach to an active approach, where I was participating in the learning process rather than being a spectator.
Passive learning on its own is more suited to incrementally adding knowledge to a solid base. Passive learning is not a good approach when you are starting something completely new from scratch. I’m not saying that passive learning is bad. It is useful to do some passive learning in addition to active learning, but you shouldn’t try to learn a completely new skill from scratch using only passive learning.
How to Get Value from This Book
There are more than 40 “Here’s How” worked-through examples and more than 70 individual practices exercises in this book. That gives you more than 110 opportunities to learn and practice. Make the most of these opportunities to develop your skills; after all, that is why you purchased this book.
If you think you can get value from this book by reading it and not doing the practice exercises, let me tell you: You can’t. If you already know how to complete a task and you have done it before, then just reading is fine. However, if you don’t know how to do a task or an exercise, then you should practice in front of your computer. First try to do an exercise without looking at the answers. If you can’t work it out, then reread the worked-through examples (labelled “Here’s How”) and then try to do the exercise again. Practice, practice, practice until you have the knowledge committed to memory and you can do it without looking.
Don’t Treat This Like a Library Book
When we were kids going to school, most of us were taught that you should not write in library books. And I guess that is fair enough. Other people will use a library book after you are finished, and they probably don’t want to read all your scribbles. Unfortunately, the message that many of us took away was “Don’t write in any book ever.” I think it is a mistake to think that you can’t write in your own books. You bought it, you own it, so why can’t you write in it? In fact, I would go one step further and say you should write in the reference books you own. You bought them for a reason: to learn. If you are reading this book and want to make some notes to yourself for future reference, then you should definitely do that.
But I guess I am forgetting the eBook revolution. I know you can’t write in an eBook, but I know you can highlight passages of text in a Kindle, and I assume you can do something similar in other eBooks. You can also type in your own notes and attach them to passages of text in many eBooks. There are lots of advantages of eBooks, and the one that means the most to me is the fact that I can have a new book in front of me just moments after I have decided to buy it.
Personally I find that eBooks are not a great fit as reference books. I prefer to have a tactile object so I can flip through the pages, add sticky notes, and so on. But that is just me, and we are all different. I am sure there are plenty of people in both camps. On the upside, eBooks are usually in colour, and printed books (like this one) are more often in black and white. Whichever camp you are in—eBook or physical book—I encourage you to write in this book and/or make notes to yourself using the eBook tools at your disposal. Doing so will make this book a more useful, personalised tool well into the future.
There Are No Pivot Tables in Power BI
In Microsoft Excel, the most common way to aggregate data for BI-style reporting is to use a pivot table. But there are no pivot tables in Power BI. What’s worse (and confronting) is that there isn’t even a spreadsheet grid for entering data on a page. Although Power BI has no pivot tables, it does allow you to use matrixes. A Power BI matrix is very similar to a pivot table and is (in my view) the best visual to use when you are learning to write DAX. Throughout this book, you will in many cases set up a matrix and then place your new measures inside that matrix so that you can visualise the results of your work. Once you have seen that the results of your measures are working as you expect, it is very easy to change the matrix into another type of visual to better display the data.
It is surprisingly difficult to create your own database of meaningful data to use for data analysis practice. Think about the data that exists in a commercial retail business, for example: customer data, finance data, sales data, products, territories, etc. And it is not a simple task to create a meaningful quantity of realistic data from scratch; it is a lot of work. Microsoft has created a number of sample databases that anyone can download and use for free. I use a modified version of the Microsoft AdventureWorks database throughout this book, provided to you in Microsoft Access format. You can download a copy of it by going to http://xbi.com.au/learndax. (Note that you do not need to have Microsoft Access installed to use this database.) This is the same sample database I use in my live training classes.
AdventureWorks contains sample data for a fictitious retail bicycle company that sells bikes and accessories in multiple countries. The data consists of the customers, products, and territories for the AdventureWorks business, along with five years of transactional sales history. The examples I use in this book therefore focus on reporting and analysis that would apply to a retail business, including such things as sales results, profit margins, customer activity, and product performance.
Clearly, not everyone who wants to learn to write DAX will operate in a retail environment. However, the retail concepts covered in this book should be familiar to everyone. So it doesn’t matter if your specific BI needs are for something other than retail. The scenarios in this book are explained throughout, so you don’t need to be a retail expert to complete or understand the exercises.
Getting Help Along the Way
Hopefully you will be able to complete the practice exercises in this book on your own. But sometimes you might need to ask someone a question before you can move forward. I encourage you to become a member of http://powerpivotforum.com.au and participate as someone who asks questions and also as someone who helps others when they get stuck. Answering questions for other people is a great way to cement your learning and build depth of knowledge. You will notice from the URL that this is an Aussie forum, but it is open to everyone. At this writing, only 15% of all traffic at the forum is from Australia, with the balance coming from more than 130 other countries around the world. I suggest that you sign up and get involved; your DAX will be better for it.
You can find a subforum dedicated to this book at http://xbi.com.au/scpbiforum. In the unfortunate event that there are errors in this book, you can go to this subforum for details.
How This Book Is Organised
I’ve organised this book to make sense to a new Power BI user. The general structure of the chapters is as follows:
This book uses best-practice naming conventions for Power Pivot and Power BI:
TableName
TableName[ColumnName]
[MeasureName]
Total Sales = SUM(Sales[ExtendedAmount])