The Definitive Guide to DAX
16%
off

The Definitive Guide to DAX : Business intelligence with Microsoft Excel, SQL Server Analysis Services, and Power BI

4.48 (31 ratings by Goodreads)
By (author)  , By (author) 

Free delivery worldwide

Available. Dispatched from the UK in 2 business days
When will my order arrive?

Description

This comprehensive and authoritative guide will teach you the DAX language for business intelligence, data modeling, and analytics. Leading 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, how DAX behaves differently from other languages, and how to use this knowledge to write fast, robust code. If you want to leverage all of DAX's remarkable power and flexibility, this no-compromise "deep dive" is exactly what you need. Perform powerful data analysis with DAX for Microsoft SQL Server Analysis Services, Excel, and Power BIMaster core DAX concepts, including calculated columns, measures, and error handlingUnderstand evaluation contexts and the CALCULATE and CALCULATETABLE functionsPerform time-based calculations: YTD, MTD, previous year, working days, and moreWork with expanded tables, complex functions, and elaborate DAX expressionsPerform calculations over hierarchies, including parent/child hierarchiesUse DAX to express diverse and unusual relationshipsMeasure DAX query performance with SQL Server Profiler and DAX Studioshow more

Product details

  • Paperback | 592 pages
  • 188 x 229 x 27.94mm | 958g
  • Microsoft Press,U.S.
  • Redmond, United States
  • English
  • 073569835X
  • 9780735698352
  • 110,218

About Alberto Ferrari

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.show more

Table of contents

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 537show more

Rating details

31 ratings
4.48 out of 5 stars
5 58% (18)
4 32% (10)
3 10% (3)
2 0% (0)
1 0% (0)
Book ratings by Goodreads
Goodreads is the world's largest site for readers with over 50 million reviews. We're featuring millions of their reader ratings on our book pages to help you find your new favourite book. Close X