- Published 10/14/2015
- 1st Edition
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
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