DAX Introduction

1 - What is DAX?

The data model

The direction of a relationship

DAX for Excel users

Cells versus tables

Excel and DAX: Two functional languages

Using iterators

DAX requires some 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

Multidimensional vs. Tabular

Hierarchies

Leaf-level calculations

2 - Introducing DAX

DAX calculations

DAX data types

DAX operators

Calculated columns

Measures

Variables

Handling errors in DAX expressions

Formatting DAX code

Common DAX functions

Aggregate functions

Logical functions

Information functions

Mathematical functions

Trigonometric functions

Text functions

Conversion functions

Date and time functions

Relational functions

Using basic table functions

Introducing table functions

EVALUATE syntax

Using table expressions

FILTER

ALL, ALLEXCEPT, and ALLNOBLANKROW

VALUES and DISTINCT

Using VALUES as a scalar value

3 - Evaluation contexts

Introduction to evaluation contexts

The row context

Testing your evaluation context understanding

Using SUM in a calculated column

Using columns in a measure

Creating a row context with iterators

Using the EARLIER function

FILTER, ALL, and context interactions

Working with many tables

Row contexts and relationships

Filter context and relationships

Introducing VALUES

Introducing ISFILTERED, ISCROSSFILTERED

Evaluation contexts recap

Creating a parameter table

4 - CALCULATE and CALCULATETABLE

CALCULATE

The filter context

Introducing CALCULATE

Filtering a single column

Filtering with complex conditions

Using CALCULATETABLE

Context transition

Context transition with measures

How many rows are visible after context transition?

Evaluation order of context transition

Variables and evaluation contexts

Circular dependencies

CALCULATE rules

Introducing ALLSELECTED

USERELATIONSHIP

5 - DAX examples

Computing ratios and percentages

Computing cumulative totals

Using ABC (Pareto) classification

Computing sales per day and working day

Computing differences in working days

Computing static moving averages

6 - Time intelligence calculations

Introduction to time intelligence

Building a Date table

Using CALENDAR and CALENDARAUTO

Working with multiple dates

Handling multiple relationships to the Date table

Handling multiple Date tables

Introduction to time intelligence

Using Mark as Date Table

Aggregating and comparing over time

Year-to-date, quarter-to-date, month-to-date

Computing periods from prior periods

Computing difference over previous periods

Computing the moving annual total

Closing balance over time

Semi-additive measures

OPENINGBALANCE and CLOSINGBALANCE functions

Advanced time intelligence

periods to date

DATEADD

FIRSTDATE and LASTDATE

FIRSTNONBLANK and LASTNONBLANK

Using drillthrough with time intelligence

Custom calendars

Working with weeks

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

Computing over noncontiguous periods

Custom comparison between periods

7 - Statistical functions

Using RANKX

Common pitfalls using RANKX

Using RANK.EQ

Computing average and moving average

Computing variance and standard deviation

Computing median and percentiles

Computing interests

Alternative implementation of PRODUCT and GEOMEAN

Using internal rate of return (XIRR)

Using net present value (XNPV)

Using Excel statistical functions

Sampling by using the SAMPLE function

Advanced table functions

EVALUATE

filter functions

projection functions

lineage and relationships

grouping/joining functions

set functions

utility functions

8 - Advanced evaluation context

ALLSELECTED

KEEPFILTERS

AutoExists

expanded tables

Difference between table expansion and filtering

Redefining the filter context

filter context intersection

filter context overwrite

arbitrarily shaped filters

the ALL function

lineage

Using advanced SetFilter

Learning and mastering evaluation contexts

9 - Handling hierarchies

Computing percentages over hierarchies

Handling parent-child hierarchies

Handling unary operators

Implementing unary operators by using DAX

10 - Advanced relationships

Using calculated physical relationships

Computing multiple-column relationships

Computing static segmentation

Using virtual relationships

Using dynamic segmentation

Many-to-many relationships

Using relationships with different granularities

Differences between physical and virtual relationships

Finding missing relationships

Computing number of products not sold

Computing new and returning customers

Examples of complex relationships

Performing currency conversion

Frequent itemset search

11 - The VertiPaq engine

database processing

Introduction to columnar databases

VertiPaq compression

value encoding

dictionary encoding

Run Length Encoding (RLE)

re-encoding

Finding the best sort order

hierarchies and relationships

segmentation and partitioning

materialization

Choosing hardware for VertiPaq

Can you choose hardware?

Set hardware priorities

CPU model

Memory speed

Number of cores

Memory size

Disk I/O and paging

12 - Optimizing data models

Gathering information about the data model

Denormalization

Columns cardinality

Handling date and time

Calculated columns

Optimizing complex filters with Boolean calculated columns

Choosing the right columns to store

Optimizing column storage

Column split optimization

Optimizing high cardinality columns

Optimizing drill-through attributes

13 - Analyzing DAX query plans

Introducing the DAX query engine

the formula engine

the storage engine (VertiPaq)

Introducing DAX query plans

Logical query plan

Physical query plan

Storage engine query

Capturing profiling information

Using the SQL Server Profiler

Using DAX Studio

Reading storage engine queries

Introducing xmSQL syntax

scan time

DISTINCTCOUNT internals

parallelism and datacache

the VertiPaq cache

CallbackDataID

Reading query plans

14 - Optimizing DAX

Defining optimization strategy

Identifying a single DAX expression to optimize

Creating a reproduction query

Analyzing server timings and query plan information

Identifying bottlenecks in the storage engine or formula engine

Optimizing bottlenecks in the storage engine

Choosing ADDCOLUMNS vs. SUMMARIZE

Reducing CallbackDataID impact

Optimizing filter conditions

Optimizing IF conditions

Optimizing cardinality

Optimizing nested iterators

Optimizing bottlenecks in the formula engine

Creating repro in MDX

Reducing materialization

Optimizing complex bottlenecks