The Definitive Guide to DAX: Business Intelligence for Microsoft Power BI, SQL Server Analysis Services, and Excel Second Edition (Business Skills)

Now expanded and updated with modern best practices, this is the most complete guide to Microsoft’s DAX language for business intelligence, data modeling, and analytics. Expert Microsoft BI consultants Marco Russo and Alberto Ferrari help you master everything from table functions through advanced code and model optimization. You’ll learn exactly what happens under the hood when you run a DAX expression, and use this knowledge to write fast, robust code. This edition focuses on examples you can build and run with the free Power BI Desktop, and helps you make the most of the powerful syntax of variables (VAR) in Power BI, Excel, or Analysis Services. Want to leverage all of DAX’s remarkable capabilities? This no-compromise “deep dive” is exactly what you need. Perform powerful data analysis with DAX for Power BI, SQL Server, and Excel · Master core DAX concepts, including calculated columns, measures, and calculation groups · Work efficiently with basic and advanced table functions · Understand evaluation contexts and the CALCULATE and CALCULATETABLE functions · Perform time-based calculations · Use calculation groups and calculation items · Use syntax of variables (VAR) to write more readable, maintainable code · Express diverse and unusual relationships with DAX, including many-to-many relationships and bidirectional filters · Master advanced optimization techniques, and improve performance in aggregations · Optimize data models to achieve better compression · Measure DAX query performance with DAX Studio and learn how to optimize your DAX Title Page 2 Copyright Page 3 Contents at a Glance 5 Contents 7 Foreword 19 Acknowledgments 21 Errata, updates, and book support 22 Stay in touch 22 Introduction to the second edition 23 Introduction to the first edition 24 Who this book is for 26 Assumptions about you 26 Organization of this book 27 Conventions 30 About the companion content 30 Chapter 1. What is DAX? 32 Understanding the data model 33 DAX for Excel users 37 DAX for SQL developers 42 DAX for MDX developers 45 DAX for Power BI users 49 Chapter 2. Introducing DAX 51 Understanding DAX calculations 51 Understanding calculated columns and measures 59 Introducing variables 64 Handling errors in DAX expressions 65 Formatting DAX code 72 Introducing aggregators and iterators 75 Using common DAX functions 78 Conclusions 87 Chapter 3. Using basic table functions 88 Introducing table functions 88 Introducing EVALUATE syntax 91 Understanding FILTER 92 Introducing ALL and ALLEXCEPT 95 Understanding VALUES, DISTINCT, and the blank row 100 Using tables as scalar values 105 Introducing ALLSELECTED 108 Conclusions 110 Chapter 4. Understanding evaluation contexts 111 Introducing evaluation contexts 111 Testing your understanding of evaluation contexts 119 Using the row context with iterators 121 Understanding FILTER, ALL, and context interactions 129 Working with several tables 132 Using DISTINCT and SUMMARIZE in filter contexts 140 Conclusions 143 Chapter 5. Understanding CALCULATE and CALCULATETABLE 145 Introducing CALCULATE and CALCULATETABLE 145 Understanding context transition 176 Understanding circular dependencies 188 CALCULATE modifiers 191 CALCULATE rules 199 Chapter 6. Variables 201 Introducing VAR syntax 201 Understanding that variables are constant 203 Understanding the scope of variables 204 Using table variables 206 Understanding lazy evaluation 208 Common patterns using variables 209 Conclusions 211 Chapter 7. Working with iterators and with CALCULATE 212 Using iterators 212 Solving common scenarios with iterators 223 Conclusions 237 Chapter 8. Time intelligence calculations 239 Introducing time intelligence 239 Building a date table 242 Understanding basic time intelligence calculations 249 Introducing basic time intelligence functions 254 Understanding semi-additive calculations 267 Understanding advanced time intelligence calculations 278 Working with custom calendars 291 Conclusions 296 Chapter 9. Calculation groups 298 Introducing calculation groups 298 Creating calculation groups 300 Understanding calculation groups 307 Understanding sideways recursion 324 Using the best practices 328 Conclusions 329 Chapter 10. Working with the filter context 330 Using HASONEVALUE and SELECTEDVALUE 331 Introducing ISFILTERED and ISCROSSFILTERED 337 Understanding differences between VALUES and FILTERS 341 Understanding the difference between ALLEXCEPT and ALL/VALUES 343 Using ALL to avoid context transition 348 Using ISEMPTY 350 Introducing data lineage and TREATAS 352 Understanding arbitrarily shaped filters 357 Conclusions 366 Chapter 11. Handling hierarchies 368 Computing percentages over hierarchies 368 Handling parent/child hierarchies 374 Conclusions 390 Chapter 12. Working with tables 391 Using CALCULATETABLE 391 Manipulating tables 393 Using tables as filters 410 Creating calculated tables 418 Conclusions 422 Chapter 13. Authoring queries 424 Introducing DAX Studio 424 Understanding EVALUATE 424 Implementing common DAX query patterns 429 Understanding the auto-exists behavior in DAX queries 459 Conclusions 466 Chapter 14. Advanced DAX concepts 468 Introducing expanded tables 468 Understanding the difference between table filters and column filters 474 Understanding ALLSELECTED and shadow filter contexts 484 The ALL* family of functions 491 Understanding data lineage 494 Conclusions 497 Chapter 15. Advanced relationships 499 Implementing calculated physical relationships 499 Implementing virtual relationships 508 Understanding physical relationships in DAX 516 Using bidirectional cross-filters 519 Understanding one-to-many relationships 521 Understanding one-to-one relationships 522 Understanding many-to-many relationships 522 Choosing the right type of relationships 535 Managing granularities 536 Managing ambiguity in relationships 540 Conclusions 546 Chapter 16. Advanced calculations in DAX 548 Computing the working days between two dates 548 Showing budget and sales together 555 Computing same-store sales 558 Numbering sequences of events 564 Computing previous year sales up to last date of sales 567 Conclusions 572 Chapter 17. The DAX engines 573 Understanding the architecture of the DAX engines 573 Understanding the VertiPaq storage engine 578 Understanding the use of relationships in VertiPaq 594 Introducing materialization 597 Introducing aggregations 600 Choosing hardware for VertiPaq 602 Conclusions 607 Chapter 18. Optimizing VertiPaq 608 Gathering information about the data model 608 Denormalization 612 Columns cardinality 619 Handling date and time 620 Calculated columns 622 Choosing the right columns to store 626 Optimizing column storage 628 Managing VertiPaq Aggregations 631 Conclusions 633 Chapter 19. Analyzing DAX query plans 635 Capturing DAX queries 635 Introducing DAX query plans 637 Capturing profiling information 642 Reading VertiPaq storage engine queries 648 Reading DirectQuery storage engine queries 667 Reading query plans 671 Conclusions 677 Chapter 20. Optimizing DAX 678 Defining optimization strategies 678 Optimizing bottlenecks in DAX expressions 688 Conclusions 726 Index 727 Code Snippets 807 Now Expanded And Updated With Modern Best Practices, This Is The Most Complete Guide To Microsoft's Dax Language For Business Intelligence, Data Modeling, And Analytics. Expert Microsoft Bi Consultants Marco Russo And Alberto Ferrari Help You Master Everything From Table Functions Through Advanced Code And Model Optimization. You'll Learn Exactly What Happens Under The Hood When You Run A Dax Expression, And Use This Knowledge To Write Fast, Robust Code. This Edition Focuses On Examples You Can Build And Run With The Free Power Bi Desktop, And Helps You Make The Most Of The Powerful Syntax Of Variables (var) In Power Bi, Excel, Or Analysis Services. Want To Leverage All Of Dax's Remarkable Capabilities? This No-compromise Deep Dive Is Exactly What You Need. -- What Is Dax? -- Introducting Dax -- Using Basic Table Functions -- Understanding Evaluatin Contexts -- Understanding Calculate And Calculatetable -- Variables -- Working Iwth Iterators And With Calculate -- Time Intelligence Claculations -- Calculation Grops -- Workig With The Filter Context -- Handling Hierachies -- Working With Tables -- Authoring Queries -- Advanced Dax Concepts -- Advanced Relationships -- Advanced Calculations In Dax -- The Dax Engines -- Optimizing Vertipaq -- Analyzing Dax Query Plans -- Optimizing Dax. Marco Russo And Alberto Ferrari. Includes Index.