Hjem

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: