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

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

Sample Content

Table of Contents

Foreword    xvii
Introduction    xix
Chapter 1: What is DAX?    1
Understanding the data model    1
Understanding the direction of a relationship    3
DAX for Excel users    5
Cells versus tables    5
Excel and DAX: Two functional languages    8
Using iterators    8
DAX requires some theory    8
DAX for SQL developers    9
Understanding relationship handling    9
DAX is a functional language    10
DAX as a programming and querying language    11
Subqueries and conditions in DAX and SQL    12
DAX for MDX developers    13
Multidimensional vs. Tabular    13
DAX as a programming and querying language    13
Hierarchies    14
Leaf-level calculations    15
Chapter 2: Introducing DAX    17
Understanding DAX calculations    17
DAX data types    18
DAX operators    21
Understanding calculated columns and measures    22
Calculated columns    22
Measures    23
Variables    26
Handling errors in DAX expressions    26
Conversion errors    26
Arithmetical operations errors    27
Intercepting errors    30
Formatting DAX code    32
Common DAX functions    35
Aggregate functions    35
Logical functions    37
Information functions    39
Mathematical functions    39
Trigonometric functions    40
Text functions    40
Conversion functions    41
Date and time functions    42
Relational functions    42
Chapter 3: Using basic table functions    45
Introducing table functions    45
EVALUATE syntax    47
Using table expressions    50
Understanding FILTER    51
Understanding ALL, ALLEXCEPT, and ALLNOBLANKROW    54
Understanding VALUES and DISTINCT    58
Using VALUES as a scalar value    59
Chapter 4: Understanding evaluation contexts    61
Introduction to evaluation contexts    62
Understanding the row context    66
Testing your evaluation context understanding    67
Using SUM in a calculated column    67
Using columns in a measure    68
Creating a row context with iterators    69
Using the EARLIER function    70
Understanding FILTER, ALL, and context interactions    74
Working with many tables    77
Row contexts and relationships    78
Filter context and relationships    80
Introducing VALUES    84
Introducing ISFILTERED, ISCROSSFILTERED    85
Evaluation contexts recap    88
Creating a parameter table    89
Chapter 5: Understanding CALCULATE and CALCULATETABLE    93
Understanding CALCULATE    93
Understanding the filter context    95
Introducing CALCULATE    98
CALCULATE examples    101
Filtering a single column    101
Filtering with complex conditions    106
Using CALCULATETABLE    109
Understanding context transition    111
Understanding context transition with measures 

Updates

Submit Errata