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

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

by Alberto Ferrari, Marco Russo
Released August 2019
Publisher(s): Microsoft Press
ISBN: 9780134865867

Read it now on the O’Reilly learning platform with a 10-day free trial.

O’Reilly members get unlimited access to live online training experiences, plus books, videos, and digital content from O’Reilly and nearly 200 trusted publishing partners.

Start your free trial

Book description

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

  1. Cover Page
  2. Title Page
  3. Copyright Page
  4. Contents at a Glance
  5. Contents
  6. Foreword
  7. Acknowledgments
    1. Errata, updates, and book support
    2. Stay in touch
  8. Introduction to the second edition
  9. Introduction to the first edition
    1. Who this book is for
    2. Assumptions about you
    3. Organization of this book
    4. Conventions
    5. About the companion content
  10. Chapter 1. What is DAX?
    1. Understanding the data model
    2. DAX for Excel users
    3. DAX for SQL developers
    4. DAX for MDX developers
    5. DAX for Power BI users
  11. Chapter 2. Introducing DAX
    1. Understanding DAX calculations
    2. Understanding calculated columns and measures
    3. Introducing variables
    4. Handling errors in DAX expressions
    5. Formatting DAX code
    6. Introducing aggregators and iterators
    7. Using common DAX functions
    8. Conclusions
  12. Chapter 3. Using basic table functions
    1. Introducing table functions
    2. Introducing EVALUATE syntax
    3. Understanding FILTER
    4. Introducing ALL and ALLEXCEPT
    5. Understanding VALUES, DISTINCT, and the blank row
    6. Using tables as scalar values
    7. Introducing ALLSELECTED
    8. Conclusions
  13. Chapter 4. Understanding evaluation contexts
    1. Introducing evaluation contexts
    2. Testing your understanding of evaluation contexts
    3. Using the row context with iterators
    4. Understanding FILTER, ALL, and context interactions
    5. Working with several tables
    6. Using DISTINCT and SUMMARIZE in filter contexts
    7. Conclusions
  14. Chapter 5. Understanding CALCULATE and CALCULATETABLE
    1. Introducing CALCULATE and CALCULATETABLE
    2. Understanding context transition
    3. Understanding circular dependencies
    4. CALCULATE modifiers
    5. CALCULATE rules
  15. Chapter 6. Variables
    1. Introducing VAR syntax
    2. Understanding that variables are constant
    3. Understanding the scope of variables
    4. Using table variables
    5. Understanding lazy evaluation
    6. Common patterns using variables
    7. Conclusions
  16. Chapter 7. Working with iterators and with CALCULATE
    1. Using iterators
    2. Solving common scenarios with iterators
    3. Conclusions
  17. Chapter 8. Time intelligence calculations
    1. Introducing time intelligence
    2. Building a date table
    3. Understanding basic time intelligence calculations
    4. Introducing basic time intelligence functions
    5. Understanding semi-additive calculations
    6. Understanding advanced time intelligence calculations
    7. Working with custom calendars
    8. Conclusions
  18. Chapter 9. Calculation groups
    1. Introducing calculation groups
    2. Creating calculation groups
    3. Understanding calculation groups
    4. Understanding sideways recursion
    5. Using the best practices
    6. Conclusions
  19. Chapter 10. Working with the filter context
    1. Using HASONEVALUE and SELECTEDVALUE
    2. Introducing ISFILTERED and ISCROSSFILTERED
    3. Understanding differences between VALUES and FILTERS
    4. Understanding the difference between ALLEXCEPT and ALL/VALUES
    5. Using ALL to avoid context transition
    6. Using ISEMPTY
    7. Introducing data lineage and TREATAS
    8. Understanding arbitrarily shaped filters
    9. Conclusions
  20. Chapter 11. Handling hierarchies
    1. Computing percentages over hierarchies
    2. Handling parent/child hierarchies
    3. Conclusions
  21. Chapter 12. Working with tables
    1. Using CALCULATETABLE
    2. Manipulating tables
    3. Using tables as filters
    4. Creating calculated tables
    5. Conclusions
  22. Chapter 13. Authoring queries
    1. Introducing DAX Studio
    2. Understanding EVALUATE
    3. Implementing common DAX query patterns
    4. Understanding the auto-exists behavior in DAX queries
    5. Conclusions
  23. Chapter 14. Advanced DAX concepts
    1. Introducing expanded tables
    2. Understanding the difference between table filters and column filters
    3. Understanding ALLSELECTED and shadow filter contexts
    4. The ALL* family of functions
    5. Understanding data lineage
    6. Conclusions
  24. Chapter 15. Advanced relationships
    1. Implementing calculated physical relationships
    2. Implementing virtual relationships
    3. Understanding physical relationships in DAX
    4. Using bidirectional cross-filters
    5. Understanding one-to-many relationships
    6. Understanding one-to-one relationships
    7. Understanding many-to-many relationships
    8. Choosing the right type of relationships
    9. Managing granularities
    10. Managing ambiguity in relationships
    11. Conclusions
  25. Chapter 16. Advanced calculations in DAX
    1. Computing the working days between two dates
    2. Showing budget and sales together
    3. Computing same-store sales
    4. Numbering sequences of events
    5. Computing previous year sales up to last date of sales
    6. Conclusions
  26. Chapter 17. The DAX engines
    1. Understanding the architecture of the DAX engines
    2. Understanding the VertiPaq storage engine
    3. Understanding the use of relationships in VertiPaq
    4. Introducing materialization
    5. Introducing aggregations
    6. Choosing hardware for VertiPaq
    7. Conclusions
  27. Chapter 18. Optimizing VertiPaq
    1. Gathering information about the data model
    2. Denormalization
    3. Columns cardinality
    4. Handling date and time
    5. Calculated columns
    6. Choosing the right columns to store
    7. Optimizing column storage
    8. Managing VertiPaq Aggregations
    9. Conclusions
  28. Chapter 19. Analyzing DAX query plans
    1. Capturing DAX queries
    2. Introducing DAX query plans
    3. Capturing profiling information
    4. Reading VertiPaq storage engine queries
    5. Reading DirectQuery storage engine queries
    6. Reading query plans
    7. Conclusions
  29. Chapter 20. Optimizing DAX
    1. Defining optimization strategies
    2. Optimizing bottlenecks in DAX expressions
    3. Conclusions
  30. Index
  31. Code Snippets

Product information

  • Title: Definitive Guide to DAX, The: Business intelligence for Microsoft Power BI, SQL Server Analysis Services, and Excel, 2nd Edition
  • Author(s): Alberto Ferrari, Marco Russo
  • Release date: August 2019
  • Publisher(s): Microsoft Press
  • ISBN: 9780134865867