Definitive Guide to DAX, The

Marco Russo and Alberto Ferrari are the founders of sqlbi.com, where they regularly publish articles about Microsoft Power BI, Power Pivot, DAX, and SQL Server Analysis Services. They have worked with DAX since the fi rst beta version of Power Pivot in 2009 and, during these years, sqlbi.com became one of the major sources for DAX articles and tutorials. Their courses, both in-person and online, are the major source of learning for many DAX enthusiasts. They both provide consultancy and mentoring on business intelligence (BI) using Microsoft technologies. They have written several books and papers about Power BI, DAX, and Analysis Services. They constantly help the community of DAX users providing content for the websites daxpatterns.com, daxformatter.com, and dax.guide. Marco and Alberto are also regular speakers at major international conferences, including Microsoft Ignite, PASS Summit, and SQLBits. Contact Marco at marco.russo@sqlbi.com, and contact Alberto at alberto.ferrari@sqlbi.com

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