Foreword Introduction to the second edition Introduction to the first edition Chapter 1 What is DAX? Understanding the data model Understanding the direction of a relationship DAX for Excel users Cells versus tables Excel and DAX: Two functional languages Iterators in DAX DAX requires theory DAX for SQL developers Relationship handling DAX is a functional language DAX as a programming and querying language Subqueries and conditions in DAX and SQL DAX for MDX developers Multidimensional versus Tabular DAX as a programming and querying language Hierarchies Leaf-level calculations DAX for Power BI users Chapter 2 Introducing DAX Understanding DAX calculations DAX data types DAX operators Table constructors Conditional statements Understanding calculated columns and measures Calculated columns Measures Introducing variables Handling errors in DAX expressions Conversion errors Arithmetic operations errors Intercepting errors Generating errors Formatting DAX code Introducing aggregators and iterators Using common DAX functions Aggregation functions Logical functions Information functions Mathematical functions Trigonometric functions Text functions Conversion functions Date and time functions Relational functions Conclusions Chapter 3 Using basic table functions Introducing table functions Introducing EVALUATE syntax Understanding FILTER Introducing ALL and ALLEXCEPT Understanding VALUES, DISTINCT, and the blank row Using tables as scalar values Introducing ALLSELECTED Conclusions Chapter 4 Understanding evaluation contexts Introducing evaluation contexts Understanding filter contexts Understanding the row context Testing your understanding of evaluation contexts Using SUM in a calculated column Using columns in a measure Using the row context with iterators Nested row contexts on different tables Nested row contexts on the same table Using the EARLIER function Understanding FILTER, ALL, and context interactions Working with several tables Row contexts and relationships Filter context and relationships Using DISTINCT and SUMMARIZE in filter contexts Conclusions Chapter 5 Understanding CALCULATE and CALCULATETABLE Introducing CALCULATE and CALCULATETABLE Creating filter contexts Introducing CALCULATE Using CALCULATE to compute percentages Introducing KEEPFILTERS Filtering a single column Filtering with complex conditions Evaluation order in CALCULATE Understanding context transition Row context and filter context recap Introducing context transition Context transition in calculated columns Context transition with measures Understanding circular dependencies CALCULATE modifiers Understanding USERELATIONSHIP Understanding CROSSFILTER Understanding KEEPFILTERS Understanding ALL in CALCULATE Introducing ALL and ALLSELECTED with no parameters CALCULATE rules Chapter 6 Variables Introducing VAR syntax Understanding that variables are constant Understanding the scope of variables Using table variables Understanding lazy evaluation Common patterns using variables Conclusions Chapter 7 Working with iterators and with CALCULATE Using iterators Understanding iterator cardinality Leveraging context transition in iterators Using CONCATENATEX Iterators returning tables Solving common scenarios with iterators Computing averages and moving averages Using RANKX Changing calculation granularity Conclusions Chapter 8 Time intelligence calculations Introducing time intelligence Automatic Date/Time in Power BI Automatic date columns in Power Pivot for Excel Date table template in Power Pivot for Excel Building a date table Using CALENDAR and CALENDARAUTO Working with multiple dates Handling multiple relationships to the Date table Handling multiple date tables Understanding basic time intelligence calculations Using Mark as Date Table Introducing basic time intelligence functions Using year-to-date, quarter-to-date, and month-to-date Computing time periods from prior periods Mixing time intelligence functions Computing a difference over previous periods Computing a moving annual total Using the right call order for nested time intelligence functions Understanding semi-additive calculations Using LASTDATE and LASTNONBLANK Working with opening and closing balances Understanding advanced time intelligence calculations Understanding periods to date Understanding DATEADD Understanding FIRSTDATE, LASTDATE, FIRSTNONBLANK, and LASTNONBLANK Using drillthrough with time intelligence Working with custom calendars Working with weeks Custom year-to-date, quarter-to-date, and month-to-date Conclusions Chapter 9 Calculation groups Introducing calculation groups Creating calculation groups Understanding calculation groups Understanding calculation item application Understanding calculation group precedence Including and excluding measures from calculation items Understanding sideways recursion Using the best practices Conclusions Chapter 10 Working with the filter context Using HASONEVALUE and SELECTEDVALUE Introducing ISFILTERED and ISCROSSFILTERED Understanding differences between VALUES and FILTERS Understanding the difference between ALLEXCEPT and ALL/VALUES Using ALL to avoid context transition Using ISEMPTY Introducing data lineage and TREATAS Understanding arbitrarily shaped filters Conclusions Chapter 11 Handling hierarchies Computing percentages over hierarchies Handling parent/child hierarchies Conclusions Chapter 12 Working with tables Using CALCULATETABLE Manipulating tables Using ADDCOLUMNS Using SUMMARIZE Using CROSSJOIN Using UNION Using INTERSECT Using EXCEPT Using tables as filters Implementing OR conditions Narrowing sales computation to the first year's customers Computing new customers Reusing table expressions with DETAILROWS Creating calculated tables Using SELECTCOLUMNS Creating static tables with ROW Creating static tables with DATATABLE Using GENERATESERIES Conclusions Chapter 13 Authoring queries Introducing DAX Studio Understanding EVALUATE Introducing the EVALUATE syntax Using VAR in DEFINE Using MEASURE in DEFINE Implementing common DAX query patterns Using ROW to test measures Using SUMMARIZE Using SUMMARIZECOLUMNS Using TOPN Using GENERATE and GENERATEALL Using ISONORAFTER Using ADDMISSINGITEMS Using TOPNSKIP Using GROUPBY Using NATURALINNERJOIN and NATURALLEFTOUTERJOIN Using SUBSTITUTEWITHINDEX Using SAMPLE Understanding the auto-exists behavior in DAX queries Conclusions Chapter 14 Advanced DAX concepts Introducing expanded tables Understanding RELATED Using RELATED in calculated columns Understanding the difference between table filters and column filters Using table filters in measures Understanding active relationships Difference between table expansion and filtering Context transition in expanded tables Understanding ALLSELECTED and shadow filter contexts Introducing shadow filter contexts ALLSELECTED returns the iterated rows ALLSELECTED without parameters The ALL* family of functions ALL ALLEXCEPT ALLNOBLANKROW ALLSELECTED ALLCROSSFILTERED Understanding data lineage Conclusions Chapter 15 Advanced relationships Implementing calculated physical relationships Computing multiple-column relationships Implementing relationships based on ranges Understanding circular dependency in calculated physical relationships Implementing virtual relationships Transferring filters in DAX Transferring a filter using TREATAS Transferring a filter using INTERSECT Transferring a filter using FILTER Implementing dynamic segmentation using virtual relationships Understanding physical relationships in DAX Using bidirectional cross-filters Understanding one-to-many relationships Understanding one-to-one relationships Understanding many-to-many relationships Implementing many-to-many using a bridge table Implementing many-to-many using a common dimension Implementing many-to-many using MMR weak relationships Choosing the right type of relationships Managing granularities Managing ambiguity in relationships Understanding ambiguity in active relationships Solving ambiguity in non-active relationships Conclusions Chapter 16 Advanced calculations in DAX Computing the working days between two dates Showing budget and sales together Computing same-store sales Numbering sequences of events Computing previous year sales up to last date of sales Conclusions Chapter 17 The DAX engines Understanding the architecture of the DAX engines Introducing the formula engine Introducing the storage engine Introducing the VertiPaq (in-memory) storage engine Introducing the DirectQuery storage engine Understanding data refresh Understanding the VertiPaq storage engine Introducing columnar databases Understanding VertiPaq compression Understanding segmentation and partitioning Using Dynamic Management Views Understanding the use of relationships in VertiPaq Introducing materialization Introducing aggregations Choosing hardware for VertiPaq Hardware choice as an option Set hardware priorities CPU model Memory speed Number of cores Memory size Disk I/O and paging Best practices in hardware selection Conclusions Chapter 18 Optimizing VertiPaq Gathering information about the data model Denormalization Columns cardinality Handling date and time Calculated columns Optimizing complex filters with Boolean calculated columns Processing of calculated columns Choosing the right columns to store Optimizing column storage Using column split optimization Optimizing high-cardinality columns Disabling attribute hierarchies Optimizing drill-through attributes Managing VertiPaq Aggregations Conclusions Chapter 19 Analyzing DAX query plans Capturing DAX queries Introducing DAX query plans Collecting query plans Introducing logical query plans Introducing physical query plans Introducing storage engine queries Capturing profiling information Using DAX Studio Using the SQL Server Profiler Reading VertiPaq storage engine queries Introducing xmSQL syntax Understanding scan time Understanding DISTINCTCOUNT internals Understanding parallelism and datacache Understanding the VertiPaq cache Understanding CallbackDataID Reading DirectQuery storage engine queries Analyzing composite models Using aggregations in the data model Reading query plans Conclusions Chapter 20 Optimizing DAX Defining optimization strategies Identifying a single DAX expression to optimize Creating a reproduction query Analyzing server timings and query plan information Identifying bottlenecks in the storage engine or formula engine Implementing changes and rerunning the test query Optimizing bottlenecks in DAX expressions Optimizing filter conditions Optimizing context transitions Optimizing IF conditions Reducing the impact of CallbackDataID Optimizing nested iterators Avoiding table filters for DISTINCTCOUNT Avoiding multiple evaluations by using variables Conclusions 9781509306978 TOC 5/23/2019