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

Now expanded and updated with modern best practices, this is the most complete guide to Microsoft’s DAX language for business intelligence, data modeling, and analytics. Expert 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, and use this knowledge to write fast, robust code. This edition focuses on examples you can build and run with the free Power BI Desktop, and helps you make the most of the powerful syntax of variables (VAR) in Power BI, Excel, or Analysis Services. Want to leverage all of DAX’s remarkable capabilities? This no-compromise “deep dive” is exactly what you need. Perform powerful data analysis with DAX for Power BI, SQL Server, and Excel ·         Master core DAX concepts, including calculated columns, measures, and calculation groups ·         Work efficiently with basic and advanced table functions ·         Understand evaluation contexts and the CALCULATE and CALCULATETABLE functions ·         Perform time-based calculations ·         Use calculation groups and calculation items ·         Use syntax of variables (VAR) to write more readable, maintainable code ·         Express diverse and unusual relationships with DAX, including many-to-many relationships and bidirectional filters ·         Master advanced optimization techniques, and improve performance in aggregations ·         Optimize data models to achieve better compression ·         Measure DAX query performance with DAX Studio and learn how to optimize your DAX 

Table of Contents:
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