Contents
The Definitive Guide to DAX: Business intelligence with Microsoft Power BI, SQL Server Analysis Services, and Excel
Second Edition
Marco Russo and Alberto Ferrari
Published with the authorization of Microsoft Corporation by:
Pearson Education, Inc.
Copyright 2020 by Alberto Ferrari and Marco Russo
All rights reserved. This publication is protected by copyright, and permission must be obtained from the publisher prior to any prohibited reproduction, storage in a retrieval system, or transmission in any form or by any means, electronic, mechanical, photocopying, recording, or likewise. For information regarding permissions, request forms, and the appropriate contacts within the Pearson Education Global Rights & Permissions Department, please visit www.pearsoned.com/permissions/. No patent liability is assumed with respect to the use of the information contained herein. Although every precaution has been taken in the preparation of this book, the publisher and author assume no responsibility for errors or omissions. Nor is any liability assumed for damages resulting from the use of the information contained herein.
ISBN-13: 978-1-5093-0697-8
ISBN-10: 1-5093-0697-8
Library of Congress Control Number: 2019930884
ScoutAutomatedPrintCode
Trademarks
Microsoft and the trademarks listed at http://www.microsoft.com on the Trademarks webpage are trademarks of the Microsoft group of companies. All other marks are property of their respective owners.
Warning and Disclaimer
Every effort has been made to make this book as complete and as accurate as possible, but no warranty or fitness is implied. The information provided is on an as is basis. The authors, the publisher, and Microsoft Corporation 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.
Special Sales
For information about buying this title in bulk quantities, or for special sales opportunities (which may include electronic versions; custom cover designs; and content particular to your business, training goals, marketing focus, or branding interests), please contact our corporate sales department at or (800) 382-3419.
For government sales inquiries, please contact .
For questions about sales outside the U.S., please contact .
EDITOR-IN-CHIEF
Brett Bartow
EXECUTIVE EDITOR
Loretta Yates
DEVELOPMENT EDITOR
Mark Renfrow
MANAGING EDITOR
Sandra Schroeder
SENIOR PROJECT EDITOR
Tonya Simpson
COPY EDITOR
Chuck Hutchinson
INDEXER
Ken Johnson
PROOFREADER
Abigail Manheim
TECHNICAL EDITOR
Daniil Maslyuk
EDITORIAL ASSISTANT
Cindy Teeters
COVER DESIGNER
Twist Creative, Seattle
COMPOSITOR
codeMantra
Contents at a Glance
Foreword
You may not know our names. We spend our days writing the code for the software you use in your daily job: We are part of the development team of Power BI, SQL Server Analysis Services, and...yes, we are among the authors of the DAX language and the VertiPaq engine.
The language you are going to learn using this book is our creation. We spent years working on this language, optimizing the engine, finding ways to improve the optimizer, and trying to build DAX into a simple, clean, and sound language to make your life as a data analyst easier and more productive.
But hey, this is intended to be the foreword of a book, so no more about us! Why are we writing a foreword for a book published by Marco and Alberto, the SQLBI guys? Well, because when you start learning DAX, it is a matter of a few clicks and searches on the web before you find articles written by them. You start reading their papers, learning the language, and hopefully appreciating our hard work. Having met them many years ago, we have great admiration for their deep knowledge of SQL Server Analysis Services. When the DAX adventure started, they were among the first to learn and adopt this new engine and language.
The articles, papers, and blog posts they publish and share on the web have become the source of learning for thousands of people. We write the code, but we do not spend much time teaching developers how to use it; Marco and Alberto are the ones who spread the knowledge about DAX.
Alberto and Marcos books are among a few bestsellers on this topic, and now with this new guide to DAX, they have truly created a milestone publication about the language we author and love. We write the code, they write the books, and you learn DAX, providing unprecedented analytical power to your business. This is what we love: working all together as a teamwe, they, and youto extract better insights from data.
Marius Dumitru, Architect, Power BI CTOs Office
Cristian Petculescu, Chief Architect of Power BI
Jeffrey Wang, Principal Software Engineer Manager
Christian Wade, Senior Program Manager
Acknowledgments
Writing this second edition required an entire years worth of work, three months more than the first edition. It has been a long and amazing journey, connecting people all around the world in any latitude and time zone to be able to produce the result you are going to read. We have so many people to thank for this book that we know it is impossible to write a complete list. So, thanks so much to all of you who contributed to this bookeven if you had no idea that you were doing so. Blog comments, forum posts, email discussions, chats with attendees and speakers at technical conferences, analysis of customer scenarios, and so much more have been useful to us, and many people have contributed significant ideas to this book. Moreover, big thanks to all the students of our courses: By teaching you, we got better!
That said, there are people we must mention personally, because of their particular contributions.
We want to start with Edward Melomed: He has inspired us, and we probably would not have started our journey with the DAX language without a passionate discussion we had with him several years ago and that ended with the table of contents of our first book about Power Pivot written on a napkin.
We want to thank Microsoft Press and the people who contributed to the project: They all greatly helped us along the process of book writing.
The only job longer than writing a book is the studying you must do in preparation for writing it. A group of people that we (in all friendliness) call ssas-insiders helped us get ready to write this book. A few people from Microsoft deserve a special mention as well, because they spent a lot of their precious time teaching us important concepts about Power BI and DAX: They are Marius Dumitru, Jeffrey Wang, Akshai Mirchandani, Krystian Sakowski, and Cristian Petculescu. Your help has been priceless, guys!
We also want to thank Amir Netz, Christian Wade, Ashvini Sharma, Kasper De Jonge, and T. K. Anand for their contributions to the many discussions we had about the product. We feel they helped us tremendously in strategic choices we made in this book and in our career.