The Definitive Guide to DAX: Business Intelligence for Microsoft Power BI, SQL Server Analysis Services, and Excel Second Edition (Business Skills)

1618 Pages·2019·78.507 MB·English

by  Marco Russo, Alberto Ferrari

The Definitive Guide to DAX:
Business intelligence with Microsoft
Power BI, SQL Server Analysis
Services, and Excel
Second Edition
Marco Russo and Alberto Ferrari
Published with the authorization of Microsoft Corporation by:
Pearson Education, Inc.
Copyright © 2020 by Alberto Ferrari and Marco Russo
All rights reserved. This publication is protected by copyright, and permission must be obtained from
the publisher prior to any prohibited reproduction, storage in a retrieval system, or transmission in
any form or by any means, electronic, mechanical, photocopying, recording, or likewise. For
information regarding permissions, request forms, and the appropriate contacts within the Pearson
Education Global Rights & Permissions Department, please visit www.pearsoned.com/permissions/.
No patent liability is assumed with respect to the use of the information contained herein. Although
every precaution has been taken in the preparation of this book, the publisher and author assume no
responsibility for errors or omissions. Nor is any liability assumed for damages resulting from the use
of the information contained herein.
ISBN-13: 978-1-5093-0697-8
ISBN-10: 1-5093-0697-8
Library of Congress Control Number: 2019930884
ScoutAutomatedPrintCode
Trademarks
Microsoft and the trademarks listed at http://www.microsoft.com on the “Trademarks” webpage are
trademarks of the Microsoft group of companies. All other marks are property of their respective
owners.
Warning and Disclaimer
Every effort has been made to make this book as complete and as accurate as possible, but no
warranty or fitness is implied. The information provided is on an “as is” basis. The authors, the
publisher, and Microsoft Corporation shall have neither liability nor responsibility to any person or
entity with respect to any loss or damages arising from the information contained in this book.
Special Sales
For information about buying this title in bulk quantities, or for special sales opportunities (which
may include electronic versions; custom cover designs; and content particular to your business,
training goals, marketing focus, or branding interests), please contact our corporate sales department
at [email protected] or [phone].
For government sales inquiries, please contact [email protected].
For questions about sales outside the U.S., please contact [email protected].
EDITOR-IN-CHIEF
Brett Bartow
EXECUTIVE EDITOR
Loretta Yates
DEVELOPMENT EDITOR
Mark Renfrow
MANAGING EDITOR
Sandra Schroeder
SENIOR PROJECT EDITOR
Tonya Simpson
COPY EDITOR
Chuck Hutchinson
INDEXER
Ken Johnson
PROOFREADER
Abigail Manheim
TECHNICAL EDITOR
Daniil Maslyuk
EDITORIAL ASSISTANT
Cindy Teeters
COVER DESIGNER
Twist Creative, Seattle
COMPOSITOR
codeMantra
Contents at a Glance
Foreword
Introduction to the second edition
Introduction to the first edition
CHAPTER 1 What is DAX?
CHAPTER 2 Introducing DAX
CHAPTER 3 Using basic table functions
CHAPTER 4 Understanding evaluation contexts
CHAPTER 5 Understanding CALCULATE and CALCULATETABLE
CHAPTER 6 Variables
CHAPTER 7 Working with iterators and with CALCULATE
CHAPTER 8 Time intelligence calculations
CHAPTER 9 Calculation groups
CHAPTER 10 Working with the filter context
CHAPTER 11 Handling hierarchies
CHAPTER 12 Working with tables
CHAPTER 13 Authoring queries
CHAPTER 14 Advanced DAX concepts
CHAPTER 15 Advanced relationships
CHAPTER 16 Advanced calculations in DAX
CHAPTER 17 The DAX engines
CHAPTER 18 Optimizing VertiPaq
CHAPTER 19 Analyzing DAX query plans
CHAPTER 20 Optimizing DAX
Index
Contents
Foreword
Introduction to the second edition
Introduction to the first edition
Chapter 1 What is DAX?
Understanding the data model
Understanding the direction of a relationship
DAX for Excel users
Cells versus tables
Excel and DAX: Two functional languages
Iterators in DAX
DAX requires theory
DAX for SQL developers
Relationship handling
DAX is a functional language
DAX as a programming and querying language
Subqueries and conditions in DAX and SQL
DAX for MDX developers
Multidimensional versus Tabular
DAX as a programming and querying language
Hierarchies
Leaf-level calculations
DAX for Power BI users
Chapter 2 Introducing DAX
Understanding DAX calculations
DAX data types
DAX operators
Table constructors
Conditional statements
Understanding calculated columns and measures
Calculated columns
Measures
Introducing variables
Handling errors in DAX expressions
Conversion errors
Arithmetic operations errors
Intercepting errors
Generating errors
Formatting DAX code
Introducing aggregators and iterators
Using common DAX functions
Aggregation functions
Logical functions
Information functions
Mathematical functions
Trigonometric functions
Text functions
Conversion functions
Date and time functions
Relational 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
Understanding filter contexts
Understanding the row context
Testing your understanding of evaluation contexts
Using SUM in a calculated column
Using columns in a measure
Using the row context with iterators
Nested row contexts on different tables
Nested row contexts on the same table
Using the EARLIER function
Understanding FILTER, ALL, and context interactions
Working with several tables
Row contexts and relationships
Filter context and relationships
Using DISTINCT and SUMMARIZE in filter contexts
Conclusions
Chapter 5 Understanding CALCULATE and CALCULATETABLE
Introducing CALCULATE and CALCULATETABLE
Creating filter contexts
Introducing CALCULATE
Using CALCULATE to compute percentages
Introducing KEEPFILTERS
Filtering a single column
Filtering with complex conditions
Evaluation order in CALCULATE
Understanding context transition
Row context and filter context recap
Introducing context transition
Context transition in calculated columns
Context transition with measures
Understanding circular dependencies
CALCULATE modifiers
Understanding USERELATIONSHIP
Understanding CROSSFILTER
Understanding KEEPFILTERS
Understanding ALL in CALCULATE
Introducing ALL and ALLSELECTED with no
parameters
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
Understanding iterator cardinality
Leveraging context transition in iterators
Using CONCATENATEX
Iterators returning tables
Solving common scenarios with iterators
Computing averages and moving averages
Using RANKX