Definitive Guide to DAX, The

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

Auteur

Marco Russo and Alberto Ferrari are the founders of sqlbi.com, where they regularly publish articles about Microsoft Power BI, Power Pivot, DAX, and SQL Server Analysis Services. They have worked with DAX since the fi rst beta version of Power Pivot in 2009 and, during these years, sqlbi.com became one of the major sources for DAX articles and tutorials. Their courses, both in-person and online, are the major source of learning for many DAX enthusiasts.

They both provide consultancy and mentoring on business intelligence (BI) using Microsoft technologies. They have written several books and papers about Power BI, DAX, and Analysis Services. They constantly help the community of DAX users providing content for the websites daxpatterns.com, daxformatter.com, and dax.guide.

Marco and Alberto are also regular speakers at major international conferences, including Microsoft Ignite, PASS Summit, and SQLBits. Contact Marco at marco.russo@sqlbi.com, and contact Alberto at alberto.ferrari@sqlbi.com

Contenu

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

Changing calculation granularity

Conclusions

Chapter 8 Time intelligence calculations

Introducing time intelligence

Automatic Date/Time in Power BI

Automatic date columns in Power Pivot for Excel

Date table template in Power Pivot for Excel

Building a date table

Using CALENDAR and CALENDARAUTO

Working with multiple dates

Handling multiple relationships to the Date table

Handling multiple date tables

Understanding basic time intelligence calculations

Using Mark as Date Table

Introducing basic time intelligence functions

Using year-to-date, quarter-to-date, and month-to-date

Computing time periods from prior periods

Mixing time intelligence functions

Computing a difference over previous periods

Computing a moving annual total

Using the right call order for nested time intelligence functions

Understanding semi-additive calculations

Using LASTDATE and LASTNONBLANK

Working with opening and closing balances

Understanding advanced time intelligence calculations

Understanding periods to date

Understanding DATEADD

Understanding FIRSTDATE, LASTDATE, FIRSTNONBLANK, and LASTNONBLANK

Using drillthrough with time intelligence

Working with custom calendars

Working with weeks

Custom year-to-date, quarter-to-date, and month-to-date

Conclusions

Chapter 9 Calculation groups

Introducing calculation groups

Creating calculation groups

Understanding calculation groups

Understanding calculation item application

Understanding calculation group precedence

Including and excluding measu…