The Definitive Guide to DAX is the most comprehensive and authoritative guide to Microsoft's DAX language for business intelligence and analytics. While other books cover only the basics, this guide offers expert guidance on everything intermediate-to-advanced Excel users and BI professionals need to know, from the basics to innovative high-performance techniques.

Foreword xviiIntroduction xixChapter 1: What 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 8Using iterators 8DAX requires some theory 8DAX for SQL developers 9Understanding relationship handling 9DAX is a functional language 10DAX as a programming and querying language 11Subqueries and conditions in DAX and SQL 12DAX for MDX developers 13Multidimensional vs. Tabular 13DAX as a programming and querying language 13Hierarchies 14Leaf-level calculations 15Chapter 2: Introducing DAX 17Understanding DAX calculations 17DAX data types 18DAX operators 21Understanding calculated columns and measures 22Calculated columns 22Measures 23Variables 26Handling errors in DAX expressions 26Conversion errors 26Arithmetical operations errors 27Intercepting errors 30Formatting DAX code 32Common DAX functions 35Aggregate functions 35Logical functions 37Information functions 39Mathematical functions 39Trigonometric functions 40Text functions 40Conversion functions 41Date and time functions 42Relational functions 42Chapter 3: Using basic table functions 45Introducing table functions 45EVALUATE syntax 47Using table expressions 50Understanding FILTER 51Understanding ALL, ALLEXCEPT, and ALLNOBLANKROW 54Understanding VALUES and DISTINCT 58Using VALUES as a scalar value 59Chapter 4: Understanding evaluation contexts 61Introduction to evaluation contexts 62Understanding the row context 66Testing your evaluation context understanding 67Using SUM in a calculated column 67Using columns in a measure 68Creating a row context with iterators 69Using the EARLIER function 70Understanding FILTER, ALL, and context interactions 74Working with many tables 77Row contexts and relationships 78Filter context and relationships 80Introducing VALUES 84Introducing ISFILTERED, ISCROSSFILTERED 85Evaluation contexts recap 88Creating a parameter table 89Chapter 5: Understanding CALCULATE and CALCULATETABLE 93Understanding CALCULATE 93Understanding the filter context 95Introducing CALCULATE 98CALCULATE examples 101Filtering a single column 101Filtering with complex conditions 106Using CALCULATETABLE 109Understanding context transition 111Understanding context transition with measures 114How many rows are visible after context transition? 116Understanding evaluation order of context transition 117Variables and evaluation contexts 118Understanding circular dependencies 119CALCULATE rules 122Introducing ALLSELECTED 123Understanding USERELATIONSHIP 125Chapter 6: DAX examples 129Computing ratios and percentages 129Computing cumulative totals 132Using ABC (Pareto) classification 136Computing sales per day and working day 143Computing differences in working days 150Computing static moving averages 151Chapter 7: Time intelligence calculations 155Introduction to time intelligence 155Building a Date table 156Using CALENDAR and CALENDARAUTO 157Working with multiple dates 160Handling multiple relationships to the Date table 161Handling multiple Date tables 162Introduction to time intelligence 164Using Mark as Date Table 166Aggregating and comparing over time 168Year-to-date, quarter-to-date, month-to-date 168Computing periods from prior periods 171Computing difference over previous periods 174Computing the moving annual total 175Closing balance over time 178Semi-additive measures 178OPENINGBALANCE and CLOSINGBALANCE functions 184Advanced time intelligence 188Understanding periods to date 189Understanding DATEADD 191Understanding FIRSTDATE and LASTDATE 196Understanding FIRSTNONBLANK and LASTNONBLANK 199Using drillthrough with time intelligence 200Custom calendars 200Working with weeks 201Custom year-to-date, quarter-to-date, month-to-date 204Computing over noncontiguous periods 206Custom comparison between periods 210Chapter 8: Statistical functions 213Using RANKX 213Common pitfalls using RANKX 216Using RANK.EQ 219Computing average and moving average 220Computing variance and standard deviation 222Computing median and percentiles 223Computing interests 225Alternative implementation of PRODUCT and GEOMEAN 226Using internal rate of return (XIRR) 227Using net present value (XNPV) 228Using Excel statistical functions 229Sampling by using the SAMPLE function 230Chapter 9: Advanced table functions 233Understanding EVALUATE 233Using VAR in EVALUATE 235Understanding filter functions 236Using CALCULATETABLE 236Using TOPN 239Understanding projection functions 241Using ADDCOLUMNS 241Using SELECTCOLUMNS 244Using ROW 247Understanding lineage and relationships 248Understanding grouping/joining functions 250Using SUMMARIZE 250Using SUMMARIZECOLUMNS 255Using GROUPBY 261Using ADDMISSINGITEMS 262Using NATURALINNERJOIN 265Using NATURALLEFTOUTERJOIN 266Understanding set functions 267Using CROSSJOIN 267Using UNION 269Using INTERSECT 272Using EXCEPT 274Using GENERATE, GENERATEALL 275Understanding utility functions 278Using CONTAINS 278Using LOOKUPVALUE 280Using SUBSTITUTEWITHINDEX 283Using ISONORAFTER 284Chapter 10: Advanced evaluation context 285Understanding ALLSELECTED 285Understanding KEEPFILTERS 294Understanding AutoExists 304Understanding expanded tables 307Difference between table expansion and filtering 315Redefining the filter context 316Understanding filter context intersection 318Understanding filter context overwrite 320Understanding arbitrarily shaped filters 321Understanding the ALL function 326Understanding lineage 329Using advanced SetFilter 331Learning and mastering evaluation contexts 338Chapter 11: Handling hierarchies 339Computing percentages over hierarchies 339Handling parent-child hierarchies 346Handling unary operators 358Implementing unary operators by using DAX 359Chapter 12: Advanced relationships 367Using calculated physical relationships 367Computing multiple-column relationships 367Computing static segmentation 369Using virtual relationships 371Using dynamic segmentation 371Many-to-many relationships 373Using relationships with different granularities 378Differences between physical and virtual relationships 381Finding missing relationships 382Computing number of products not sold 383Computing new and returning customers 384Examples of complex relationships 386Performing currency conversion 386Frequent itemset search 392Chapter 13: The VertiPaq engine 399Understanding database processing 400Introduction to columnar databases 400Understanding VertiPaq compression 403Understanding value encoding 404Understanding dictionary encoding 405Understanding Run Length Encoding (RLE) 406Understanding re-encoding 409Finding the best sort order 409Understanding hierarchies and relationships 410Understanding segmentation and partitioning 412Using Dynamic Management Views 413Using DISCOVER_OBJECT_MEMORY_USAGE 414Using DISCOVER_STORAGE_TABLES 414Using DISCOVER_STORAGE_TABLE_COLUMNS 415Using DISCOVER_STORAGE_TABLE_COLUMN_SEGMENTS 416Understanding materialization 417Choosing hardware for VertiPaq 421Can you choose hardware? 421Set hardware priorities 421CPU model 422Memory speed 423Number of cores 423Memory size 424Disk I/O and paging 424Conclusions 424Chapter 14: Optimizing data models 425Gathering information about the data model 425Denormalization 434Columns cardinality 442Handling date and time 443Calculated columns 447Optimizing complex filters with Boolean calculated columns 450Choosing the right columns to store 451Optimizing column storage 453Column split optimization 453Optimizing high cardinality columns 454Optimizing drill-through attributes 455Chapter 15: Analyzing DAX query plans 457Introducing the DAX query engine 457Understanding the formula engine 458Understanding the storage engine (VertiPaq) 459Introducing DAX query plans 459Logical query plan 460Physical query plan 461Storage engine query 462Capturing profiling information 463Using the SQL Server Profiler 463Using DAX Studio 467Reading storage engine queries 470Introducing xmSQL syntax 470Understanding scan time 477Understanding DISTINCTCOUNT internals 479Understanding parallelism and datacache 480Understanding the VertiPaq cache 481Understanding CallbackDataID 483Reading query plans 488Chapter 16: Optimizing DAX 495Defining optimization strategy 496Identifying a single DAX expression to optimize 496Creating a reproduction query 499Analyzing server timings and query plan information 500Identifying bottlenecks in the storage engine or formula engine 503Optimizing bottlenecks in the storage engine 504Choosing ADDCOLUMNS vs. SUMMARIZE 505Reducing CallbackDataID impact 509Optimizing filter conditions 512Optimizing IF conditions 513Optimizing cardinality 515Optimizing nested iterators 517Optimizing bottlenecks in the formula engine 522Creating repro in MDX 527Reducing materialization 528Optimizing complex bottlenecks 532Index 537