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

Cover Page
Title Page
Copyright Page
Contents at a Glance
Contents
Foreword
Acknowledgments
Errata, updates, and book support
Stay in touch
Introduction to the second edition
Introduction to the first edition
Who this book is for
Assumptions about you
Organization of this book
Conventions
About the companion content
Chapter 1. What is DAX?
Understanding the data model
DAX for Excel users
DAX for SQL developers
DAX for MDX developers
DAX for Power BI users
Chapter 2. Introducing DAX
Understanding DAX calculations
Understanding calculated columns and measures
Introducing variables
Handling errors in DAX expressions
Formatting DAX code
Introducing aggregators and iterators
Using common DAX 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
Testing your understanding of evaluation contexts
Using the row context with iterators
Understanding FILTER, ALL, and context interactions
Working with several tables
Using DISTINCT and SUMMARIZE in filter contexts
Conclusions
Chapter 5. Understanding CALCULATE and CALCULATETABLE
Introducing CALCULATE and CALCULATETABLE
Understanding context transition
Understanding circular dependencies
CALCULATE modifiers
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
Solving common scenarios with iterators
Conclusions
Chapter 8. Time intelligence calculations
Introducing time intelligence
Building a date table
Understanding basic time intelligence calculations
Introducing basic time intelligence functions
Understanding semi-additive calculations
Understanding advanced time intelligence calculations
Working with custom calendars
Conclusions
Chapter 9. Calculation groups
Introducing calculation groups
Creating calculation groups
Understanding calculation groups
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 tables as filters
Creating calculated tables
Conclusions
Chapter 13. Authoring queries
Introducing DAX Studio
Understanding EVALUATE
Implementing common DAX query patterns
Understanding the auto-exists behavior in DAX queries
Conclusions
Chapter 14. Advanced DAX concepts
Introducing expanded tables
Understanding the difference between table filters and column filters
Understanding ALLSELECTED and shadow filter contexts
The ALL* family of functions
Understanding data lineage
Conclusions
Chapter 15. Advanced relationships
Implementing calculated physical relationships
Implementing 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
Choosing the right type of relationships
Managing granularities
Managing ambiguity in 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
Understanding the VertiPaq storage engine
Understanding the use of relationships in VertiPaq
Introducing materialization
Introducing aggregations
Choosing hardware for VertiPaq
Conclusions
Chapter 18. Optimizing VertiPaq
Gathering information about the data model
Denormalization
Columns cardinality
Handling date and time
Calculated columns
Choosing the right columns to store
Optimizing column storage
Managing VertiPaq Aggregations
Conclusions
Chapter 19. Analyzing DAX query plans
Capturing DAX queries
Introducing DAX query plans
Capturing profiling information
Reading VertiPaq storage engine queries
Reading DirectQuery storage engine queries
Reading query plans
Conclusions
Chapter 20. Optimizing DAX
Defining optimization strategies
Optimizing bottlenecks in DAX expressions
Conclusions
Index
Code Snippets