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

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