The De - .NET Framework

1 The Definitive Guide to DAX: Business intelligence with Microsoft Power BI, SQL Server Analysis Services, and Excel Second Edition Marco Russo and Alberto Ferrari i 21/05/19 5:30 pm Published with the authorization of Microsoft Corporation by: EDITOR-IN-CHIEF. Pearson Education, Inc. Brett Bartow Copyright 2020 by Alberto Ferrari and Marco Russo EXECUTIVE EDITOR. Loretta Yates All rights reserved. This publication is protected by copyright, and permission must DEVELOPMENT EDITOR. 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, Mark Renfrow photocopying, recording, or likewise.

2 For information regarding permissions, request MANAGING EDITOR. forms, and the appropriate contacts within the Pearson Education Global Rights &. Permissions Department, please visit No patent Sandra Schroeder liability is assumed with respect to the use of the information contained herein. Although SENIOR PROJECT EDITOR. 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 Tonya Simpson damages resulting from the use of the information contained herein.

3 COPY EDITOR. Chuck Hutchinson ISBN-13: 978-1-5093-0697-8. INDEXER. ISBN-10: 1-5093-0697-8. Ken Johnson Library of Congress Control Number: 2019930884. ScoutAutomatedPrintCode PROOFREADER. Abigail Manheim Trademarks TECHNICAL EDITOR. Microsoft and the trademarks listed at on the Trademarks . Daniil Maslyuk webpage are trademarks of the Microsoft group of companies. All other marks are property of their respective owners. EDITORIAL ASSISTANT. Cindy Teeters Warning and Disclaimer Every effort has been made to make this book as complete and as accurate as possible, COVER DESIGNER.

4 But no warranty or fitness is implied. The information provided is on an as is basis. Twist Creative, Seattle The authors, the publisher, and Microsoft Corporation shall have neither liability nor COMPOSITOR. responsibility to any person or entity with respect to any loss or damages arising from the information contained in this book. codeMantra 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.

5 For government sales inquiries, please contact For questions about sales outside the , please contact ii 21/05/19 5:30 pm Contents at a Glance Foreword xvii Introduction to the second edition xx Introduction to the first edition xxi CHAPTER 1 What is DAX? 1. CHAPTER 2 Introducing DAX 17. CHAPTER 3 Using basic table functions 57. CHAPTER 4 Understanding evaluation contexts 79. CHAPTER 5 Understanding CALCULATE and CALCULATETABLE 115. CHAPTER 6 Variables 175. CHAPTER 7 Working with iterators and with CALCULATE 187. CHAPTER 8 Time intelligence calculations 217. CHAPTER 9 Calculation groups 279.

6 CHAPTER 10 Working with the filter context 313. CHAPTER 11 Handling hierarchies 345. CHAPTER 12 Working with tables 363. CHAPTER 13 Authoring queries 395. CHAPTER 14 Advanced DAX concepts 437. CHAPTER 15 Advanced relationships 471. CHAPTER 16 Advanced calculations in DAX 519. CHAPTER 17 The DAX engines 545. CHAPTER 18 Optimizing VertiPaq 579. CHAPTER 19 Analyzing DAX query plans 609. CHAPTER 20 Optimizing DAX 657. Index 711. iii iii 21/05/19 5:30 pm iv 21/05/19 5:30 pm Contents Foreword ..xvii Introduction to the second edition .. xx Introduction to the first edition .. xxi Chapter 1 What is DAX?

7 1. Understanding the data model .. 1. Understanding the direction of a relationship .. 3. DAX for Excel users .. 5. Cells versus tables .. 5. Excel and DAX: Two functional languages .. 7. Iterators in DAX .. 8. DAX requires theory .. 8. DAX for SQL developers .. 9. Relationship handling .. 9. DAX is a functional language .. 10. DAX as a programming and querying language .. 10. Subqueries and conditions in DAX and SQL .. 11. DAX for MDX developers .. 12. Multidimensional versus Tabular .. 12. DAX as a programming and querying language .. 12. Hierarchies .. 13. Leaf-level calculations.

8 14. DAX for Power BI users .. 14. Chapter 2 Introducing DAX 17. Understanding DAX calculations .. 17. DAX data types .. 19. DAX operators ..23. Table constructors ..24. Conditional statements ..24. v v 21/05/19 5:30 pm Understanding calculated columns and measures ..25. Calculated columns ..25. Measures ..26. Introducing variables ..30. Handling errors in DAX expressions .. 31. Conversion errors.. 31. Arithmetic operations errors ..32. Intercepting errors..35. Generating errors..38. Formatting DAX code..39. Introducing aggregators and iterators ..42. Using common DAX functions.

9 45. Aggregation functions ..45. Logical functions ..46. Information functions..48. Mathematical functions ..49. Trigonometric functions ..50. Text functions ..50. Conversion functions .. 51. Date and time functions ..52. Relational functions ..53. Conclusions ..55. Chapter 3 Using basic table functions 57. Introducing table functions ..57. Introducing EVALUATE syntax ..59. Understanding FILTER .. 61. Introducing ALL and ALLEXCEPT ..63. Understanding VALUES, DISTINCT, and the blank row ..68. Using tables as scalar values ..72. Introducing ALLSELECTED ..75. Conclusions ..77.

10 Vi Contents vi 21/05/19 5:30 pm Chapter 4 Understanding evaluation contexts 79. Introducing evaluation contexts ..80. Understanding filter contexts ..80. Understanding the row context ..85. Testing your understanding of evaluation contexts ..88. Using SUM in a calculated column ..88. Using columns in a measure ..89. Using the row context with iterators ..90. Nested row contexts on different tables .. 91. Nested row contexts on the same table ..92. Using the EARLIER function ..97. Understanding FILTER, ALL, and context interactions ..98. Working with several tables .. 101.