You are previewing The Definitive Guide to DAX: Business intelligence with Microsoft Excel, SQL Server Analysis Services, and Power BI.
O'Reilly logo
The Definitive Guide to DAX: Business intelligence with Microsoft Excel, SQL Server Analysis Services, and Power BI

Book Description

This comprehensive and authoritative guide will teach you the DAX language for business intelligence, data modeling, and analytics. Leading 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, how DAX behaves differently from other languages, and how to use this knowledge to write fast, robust code. If you want to leverage all of DAX’s remarkable power and flexibility, this no-compromise “deep dive” is exactly what you need.
 
Perform powerful data analysis with DAX for Microsoft SQL Server Analysis Services, Excel, and Power BI

  • Master core DAX concepts, including calculated columns, measures, and error handling

  • Understand evaluation contexts and the CALCULATE and CALCULATETABLE functions

  • Perform time-based calculations: YTD, MTD, previous year, working days, and more

  • Work with expanded tables, complex functions, and elaborate DAX expressions

  • Perform calculations over hierarchies, including parent/child hierarchies

  • Use DAX to express diverse and unusual relationships

  • Measure DAX query performance with SQL Server Profiler and DAX Studio

  • Table of Contents

    1. Cover
    2. Title Page
    3. Copyright Page
    4. Dedication Page
    5. Contents at a glance
    6. Table of contents
    7. Foreword
    8. Introduction
      1. Who this book is for
      2. Assumptions about you
      3. Organization of this book
      4. Conventions
      5. About the companion content
      6. Acknowledgments
      7. Errata, updates, and book support
      8. Free ebooks from Microsoft Press
      9. We want to hear from you
      10. Stay in touch
    9. Chapter 1. What is DAX?
      1. Understanding the data model
        1. Understanding the direction of a relationship
      2. DAX for Excel users
        1. Cells versus tables
        2. Excel and DAX: Two functional languages
        3. Using iterators
        4. DAX requires some theory
      3. DAX for SQL developers
        1. Understanding relationship handling
        2. DAX is a functional language
        3. DAX as a programming and querying language
        4. Subqueries and conditions in DAX and SQL
      4. DAX for MDX developers
        1. Multidimensional vs. Tabular
        2. DAX as a programming and querying language
        3. Hierarchies
        4. Leaf-level calculations
    10. Chapter 2. Introducing DAX
      1. Understanding DAX calculations
        1. DAX data types
        2. DAX operators
      2. Understanding calculated columns and measures
        1. Calculated columns
        2. Measures
      3. Variables
      4. Handling errors in DAX expressions
        1. Conversion errors
        2. Arithmetical operations errors
        3. Intercepting errors
      5. Formatting DAX code
      6. Common DAX functions
        1. Aggregate functions
        2. Logical functions
        3. Information functions
        4. Mathematical functions
        5. Trigonometric functions
        6. Text functions
        7. Conversion functions
        8. Date and time functions
        9. Relational functions
    11. Chapter 3. Using basic table functions
      1. Introducing table functions
      2. EVALUATE syntax
      3. Using table expressions
      4. Understanding FILTER
      5. Understanding ALL, ALLEXCEPT, and ALLNOBLANKROW
      6. Understanding VALUES and DISTINCT
        1. Using VALUES as a scalar value
    12. Chapter 4. Understanding evaluation contexts
      1. Introduction to evaluation contexts
        1. Understanding the row context
      2. Testing your evaluation context understanding
        1. Using SUM in a calculated column
        2. Using columns in a measure
      3. Creating a row context with iterators
        1. Using the EARLIER function
      4. Understanding FILTER, ALL, and context interactions
      5. Working with many tables
        1. Row contexts and relationships
        2. Filter context and relationships
        3. Introducing VALUES
        4. Introducing ISFILTERED, ISCROSSFILTERED
      6. Evaluation contexts recap
      7. Creating a parameter table
    13. Chapter 5. Understanding CALCULATE and CALCULATETABLE
      1. Understanding CALCULATE
        1. Understanding the filter context
        2. Introducing CALCULATE
      2. CALCULATE examples
        1. Filtering a single column
        2. Filtering with complex conditions
        3. Using CALCULATETABLE
      3. Understanding context transition
        1. Understanding context transition with measures
        2. How many rows are visible after context transition?
        3. Understanding evaluation order of context transition
      4. Variables and evaluation contexts
      5. Understanding circular dependencies
      6. CALCULATE rules
      7. Introducing ALLSELECTED
      8. Understanding USERELATIONSHIP
    14. Chapter 6. DAX examples
      1. Computing ratios and percentages
      2. Computing cumulative totals
      3. Using ABC (Pareto) classification
      4. Computing sales per day and working day
      5. Computing differences in working days
      6. Computing static moving averages
    15. Chapter 7. Time intelligence calculations
      1. Introduction to time intelligence
      2. Building a Date table
        1. Using CALENDAR and CALENDARAUTO
      3. Working with multiple dates
        1. Handling multiple relationships to the Date table
        2. Handling multiple Date tables
      4. Introduction to time intelligence
        1. Using Mark as Date Table
      5. Aggregating and comparing over time
        1. Year-to-date, quarter-to-date, month-to-date
        2. Computing periods from prior periods
        3. Computing difference over previous periods
        4. Computing the moving annual total
      6. Closing balance over time
        1. Semi-additive measures
        2. OPENINGBALANCE and CLOSINGBALANCE functions
      7. Advanced time intelligence
        1. Understanding periods to date
        2. Understanding DATEADD
        3. Understanding FIRSTDATE and LASTDATE
        4. Understanding FIRSTNONBLANK and LASTNONBLANK
        5. Using drillthrough with time intelligence
      8. Custom calendars
        1. Working with weeks
        2. Custom year-to-date, quarter-to-date, month-to-date
        3. Computing over noncontiguous periods
        4. Custom comparison between periods
    16. Chapter 8. Statistical functions
      1. Using RANKX
        1. Common pitfalls using RANKX
      2. Using RANK.EQ
      3. Computing average and moving average
      4. Computing variance and standard deviation
      5. Computing median and percentiles
      6. Computing interests
        1. Alternative implementation of PRODUCT and GEOMEAN
        2. Using internal rate of return (XIRR)
        3. Using net present value (XNPV)
      7. Using Excel statistical functions
      8. Sampling by using the SAMPLE function
    17. Chapter 9. Advanced table functions
      1. Understanding EVALUATE
        1. Using VAR in EVALUATE
      2. Understanding filter functions
        1. Using CALCULATETABLE
        2. Using TOPN
      3. Understanding projection functions
        1. Using ADDCOLUMNS
        2. Using SELECTCOLUMNS
        3. Using ROW
      4. Understanding lineage and relationships
      5. Understanding grouping/joining functions
        1. Using SUMMARIZE
        2. Using SUMMARIZECOLUMNS
        3. Using GROUPBY
        4. Using ADDMISSINGITEMS
        5. Using NATURALINNERJOIN
        6. Using NATURALLEFTOUTERJOIN
      6. Understanding set functions
        1. Using CROSSJOIN
        2. Using UNION
        3. Using INTERSECT
        4. Using EXCEPT
        5. Using GENERATE, GENERATEALL
      7. Understanding utility functions
        1. Using CONTAINS
        2. Using LOOKUPVALUE
        3. Using SUBSTITUTEWITHINDEX
        4. Using ISONORAFTER
    18. Chapter 10. Advanced evaluation context
      1. Understanding ALLSELECTED
      2. Understanding KEEPFILTERS
      3. Understanding AutoExists
      4. Understanding expanded tables
        1. Difference between table expansion and filtering
      5. Redefining the filter context
        1. Understanding filter context intersection
        2. Understanding filter context overwrite
        3. Understanding arbitrarily shaped filters
      6. Understanding the ALL function
      7. Understanding lineage
      8. Using advanced SetFilter
      9. Learning and mastering evaluation contexts
    19. Chapter 11. Handling hierarchies
      1. Computing percentages over hierarchies
      2. Handling parent-child hierarchies
      3. Handling unary operators
        1. Implementing unary operators by using DAX
    20. Chapter 12. Advanced relationships
      1. Using calculated physical relationships
        1. Computing multiple-column relationships
        2. Computing static segmentation
      2. Using virtual relationships
        1. Using dynamic segmentation
        2. Many-to-many relationships
        3. Using relationships with different granularities
        4. Differences between physical and virtual relationships
      3. Finding missing relationships
        1. Computing number of products not sold
        2. Computing new and returning customers
      4. Examples of complex relationships
        1. Performing currency conversion
        2. Frequent itemset search
    21. Chapter 13. The VertiPaq engine
      1. Understanding database processing
      2. Introduction to columnar databases
      3. Understanding VertiPaq compression
        1. Understanding value encoding
        2. Understanding dictionary encoding
        3. Understanding Run Length Encoding (RLE)
        4. Understanding re-encoding
        5. Finding the best sort order
        6. Understanding hierarchies and relationships
      4. Understanding segmentation and partitioning
      5. Using Dynamic Management Views
        1. Using DISCOVER_OBJECT_MEMORY_USAGE
        2. Using DISCOVER_STORAGE_TABLES
        3. Using DISCOVER_STORAGE_TABLE_COLUMNS
        4. Using DISCOVER_STORAGE_TABLE_COLUMN_SEGMENTS
      6. Understanding materialization
      7. Choosing hardware for VertiPaq
        1. Can you choose hardware?
        2. Set hardware priorities
        3. CPU model
        4. Memory speed
        5. Number of cores
        6. Memory size
        7. Disk I/O and paging
        8. Conclusions
    22. Chapter 14. Optimizing data models
      1. Gathering information about the data model
      2. Denormalization
      3. Columns cardinality
        1. Handling date and time
      4. Calculated columns
        1. Optimizing complex filters with Boolean calculated columns
      5. Choosing the right columns to store
      6. Optimizing column storage
        1. Column split optimization
        2. Optimizing high cardinality columns
        3. Optimizing drill-through attributes
    23. Chapter 15. Analyzing DAX query plans
      1. Introducing the DAX query engine
        1. Understanding the formula engine
        2. Understanding the storage engine (VertiPaq)
      2. Introducing DAX query plans
        1. Logical query plan
        2. Physical query plan
        3. Storage engine query
      3. Capturing profiling information
        1. Using the SQL Server Profiler
        2. Using DAX Studio
      4. Reading storage engine queries
        1. Introducing xmSQL syntax
        2. Understanding scan time
        3. Understanding DISTINCTCOUNT internals
        4. Understanding parallelism and datacache
        5. Understanding the VertiPaq cache
        6. Understanding CallbackDataID
      5. Reading query plans
    24. Chapter 16. Optimizing DAX
      1. Defining optimization strategy
        1. Identifying a single DAX expression to optimize
        2. Creating a reproduction query
        3. Analyzing server timings and query plan information
        4. Identifying bottlenecks in the storage engine or formula engine
      2. Optimizing bottlenecks in the storage engine
        1. Choosing ADDCOLUMNS vs. SUMMARIZE
        2. Reducing CallbackDataID impact
        3. Optimizing filter conditions
        4. Optimizing IF conditions
        5. Optimizing cardinality
        6. Optimizing nested iterators
      3. Optimizing bottlenecks in the formula engine
        1. Creating repro in MDX
        2. Reducing materialization
      4. Optimizing complex bottlenecks
    25. Index
    26. About the authors
    27. Free ebooks
    28. Survey
    29. Code Snippets