Transcription
The Definitive Guide toDAX: Business intelligencewith Microsoft PowerBI, SQL Server AnalysisServices, and ExcelSecond EditionMarco Russo and Alberto Ferrari9781509306978 print.indb i21/05/19 5:30 pm
Published with the authorization of Microsoft Corporation by:EDITOR-IN-CHIEFPearson Education, Inc.Brett BartowCopyright 2020 by Alberto Ferrari and Marco RussoEXECUTIVE EDITORLoretta YatesAll rights reserved. This publication is protected by copyright, and permission mustbe obtained from the publisher prior to any prohibited reproduction, storage in aretrieval system, or transmission in any form or by any means, electronic, mechanical,photocopying, recording, or likewise. For information regarding permissions, requestforms, and the appropriate contacts within the Pearson Education Global Rights &Permissions Department, please visit www.pearsoned.com/permissions/. No patentliability is assumed with respect to the use of the information contained herein. Althoughevery precaution has been taken in the preparation of this book, the publisher andauthor assume no responsibility for errors or omissions. Nor is any liability assumed fordamages resulting from the use of the information contained herein.ISBN-13: 978-1-5093-0697-8ISBN-10: 1-5093-0697-8Library of Congress Control Number: ft and the trademarks listed at http://www.microsoft.com on the “Trademarks”webpage are trademarks of the Microsoft group of companies. All other marks areproperty of their respective owners.Warning and DisclaimerEvery 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 norresponsibility to any person or entity with respect to any loss or damages arising fromthe information contained in this book.DEVELOPMENT EDITORMark RenfrowMANAGING EDITORSandra SchroederSENIOR PROJECT EDITORTonya SimpsonCOPY EDITORChuck HutchinsonINDEXERKen JohnsonPROOFREADERAbigail ManheimTECHNICAL EDITORDaniil MaslyukEDITORIAL ASSISTANTCindy TeetersCOVER DESIGNERTwist Creative, SeattleCOMPOSITORcodeMantraSpecial SalesFor information about buying this title in bulk quantities, or for special salesopportunities (which may include electronic versions; custom cover designs; andcontent particular to your business, training goals, marketing focus, or brandinginterests), please contact our corporate sales department at corpsales@pearsoned.comor (800) 382-3419.For government sales inquiries, please contact governmentsales@pearsoned.com.For questions about sales outside the U.S., please contact intlcs@pearson.com.9781509306978 print.indb ii21/05/19 5:30 pm
Contents at a GlanceForewordxviiIntroduction to the second editionxxIntroduction to the first editionxxiCHAPTER 1What is DAX?CHAPTER 2Introducing DAX17CHAPTER 3Using basic table functions57CHAPTER 4Understanding evaluation contexts79CHAPTER 5Understanding CALCULATE and CALCULATETABLE115CHAPTER 6Variables175CHAPTER 7Working with iterators and with CALCULATE187CHAPTER 8Time intelligence calculations217CHAPTER 9Calculation groups279CHAPTER 10Working with the filter context313CHAPTER 11Handling hierarchies345CHAPTER 12Working with tables363CHAPTER 13Authoring queries395CHAPTER 14Advanced DAX concepts437CHAPTER 15Advanced relationships471CHAPTER 16Advanced calculations in DAX519CHAPTER 17The DAX engines545CHAPTER 18Optimizing VertiPaq579CHAPTER 19Analyzing DAX query plans609CHAPTER 20Optimizing DAX657Index1711iii9781509306978 print.indb iii21/05/19 5:30 pm
9781509306978 print.indb iv21/05/19 5:30 pm
ContentsForeword . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .xviiIntroduction to the second edition . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xxIntroduction to the first edition . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xxiChapter 1What is DAX?1Understanding the data model . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1Understanding the direction of a relationship . . . . . . . . . . . . . . . . . . . . . 3DAX for Excel users . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5Cells versus tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5Excel and DAX: Two functional languages . . . . . . . . . . . . . . . . . . . . . . . . . 7Iterators in DAX . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8DAX requires theory . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8DAX for SQL developers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 9Relationship handling . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 9DAX is a functional language . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 10DAX as a programming and querying language . . . . . . . . . . . . . . . . . . 10Subqueries and conditions in DAX and SQL . . . . . . . . . . . . . . . . . . . . . . 11DAX for MDX developers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 12Multidimensional versus Tabular . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 12DAX as a programming and querying language . . . . . . . . . . . . . . . . . . 12Hierarchies . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 13Leaf-level calculations . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 14DAX for Power BI users . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 14Chapter 2Introducing DAX17Understanding DAX calculations . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 17DAX data types . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 19DAX operators . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .23Table constructors . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .24Conditional statements . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .24v9781509306978 print.indb v21/05/19 5:30 pm
Understanding calculated columns and measures . . . . . . . . . . . . . . . . . . . . . .25Calculated columns . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .25Measures . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .26Introducing variables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .30Handling errors in DAX expressions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 31Conversion errors. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 31Arithmetic operations errors . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .32Intercepting errors. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .35Generating errors. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .38Formatting DAX code. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .39Introducing aggregators and iterators . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .42Using common DAX functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .45Aggregation functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .45Logical functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .46Information functions. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .48Mathematical functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .49Trigonometric functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .50Text functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .50Conversion functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 51Date and time functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .52Relational functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .53Conclusions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .55Chapter 3Using basic table functions57Introducing table functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .57Introducing EVALUATE syntax . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .59Understanding FILTER . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 61Introducing ALL and ALLEXCEPT . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .63Understanding VALUES, DISTINCT, and the blank row . . . . . . . . . . . . . . . . .68Using tables as scalar values . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .72Introducing ALLSELECTED . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .75Conclusions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .77viContents9781509306978 print.indb vi21/05/19 5:30 pm
Chapter 4Understanding evaluation contexts79Introducing evaluation contexts . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .80Understanding filter contexts . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .80Understanding the row context . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .85Testing your understanding of evaluation contexts . . . . . . . . . . . . . . . . . . . . .88Using SUM in a calculated column . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .88Using columns in a measure . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .89Using the row context with iterators . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .90Nested row contexts on different tables . . . . . . . . . . . . . . . . . . . . . . . . . 91Nested row contexts on the same table . . . . . . . . . . . . . . . . . . . . . . . . . .92Using the EARLIER function . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .97Understanding FILTER, ALL, and context interactions . . . . . . . . . . . . . . . . . .98Working with several tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 101Row contexts and relationships . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .102Filter context and relationships . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .106Using DISTINCT and SUMMARIZE in filter contexts . . . . . . . . . . . . . . . . . .109Conclusions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 113Chapter 5Understanding CALCULATE andCALCULATETABLE115Introducing CALCULATE and CALCULATETABLE. . . . . . . . . . . . . . . . . . . . . 115Creating filter contexts . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 115Introducing CALCULATE . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 119Using CALCULATE to compute percentages . . . . . . . . . . . . . . . . . . . .124Introducing KEEPFILTERS . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .135Filtering a single column . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .138Filtering with complex conditions. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .140Evaluation order in CALCULATE . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .144Understanding context transition . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .148Row context and filter context recap . . . . . . . . . . . . . . . . . . . . . . . . . . . .148Introducing context transition . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 151Context transition in calculated columns . . . . . . . . . . . . . . . . . . . . . . . .154Context transition with measures . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 157Contents9781509306978 print.indb viivii21/05/19 5:30 pm
Understanding circular dependencies . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 161CALCULATE modifiers. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .164Understanding USERELATIONSHIP . . . . . . . . . . . . . . . . . . . . . . . . . . . .164Understanding CROSSFILTER . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .168Understanding KEEPFILTERS . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .168Understanding ALL in CALCULATE . . . . . . . . . . . . . . . . . . . . . . . . . . . . .169Introducing ALL and ALLSELECTED with no parameters . . . . . . . . . 171CALCULATE rules . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 172Chapter 6Variables175Introducing VAR syntax. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
The Defi nitive Guide to DAX: Business intelligence with Microsoft Power BI, SQL Server Analysis Services, and Excel Second Edition Marco Russo and Alberto Ferrari
