Overview

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.

Full Product Details

Author:   Alberto Ferrari ,  Marco Russo
Publisher:   Microsoft Press,U.S.
Imprint:   Microsoft Press,U.S.
Dimensions:   Width: 19.00cm , Height: 3.00cm , Length: 23.00cm
Weight:   0.952kg
ISBN:  

9780735698352

ISBN 10:   073569835
Pages:   592
Publication Date:   13 October 2015
Audience:   Professional and scholarly ,  Professional & Vocational
Replaced By:   9781509306978
Format:   Paperback
Publisher's Status:   Active
Availability:   In Print  
This item will be ordered in for you from one of our suppliers. Upon receipt, we will promptly dispatch it out to you. For in store availability, please contact us.

Table of Contents

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

Reviews

Author Information

Marco Russo and Alberto Ferrari cofounded sqlbi.com, where they regularly publish articles about Microsoft PowerPivot, DAX, Power BI, and SQL Server Analysis Services. They also speak regularly at major international conferences, such as TechEd, Ignite, PASS Summit, and SQLBits. Both consult and mentor on business intelligence (BI) and Microsoft BI technologies, having worked with them since 1999. Their books include Microsoft Excel 2013 Building Data Models with PowerPivot, Microsoft SQL Server 2012 Analysis Services: The BISM Tabular Model, and Microsoft PowerPivot for Excel 2010: Give Your Data Meaning.

Tab Content 6

Author Website:  

Customer Reviews

Recent Reviews

No review item found!

Add your own review!

Countries Available

All regions