Microsoft Power BI DAX Training Course

Course Introduction

What is DAX

DAX Functions

DAX Contexts

What is Vertipaq

DAX efficiency

DAX references

DAX best practices

Recap of Contexts

Introduction to Contexts

Recap of DAX Contexts

The Row Context

The Filter Context

Measures and Calculated Columns

Dataset for exercises – Computer Store sales

Filter and Row Context in a measure

The CALCULATE function

Context Transition

Filter Expressions of CALCULATE

Rules of Evaluation

CALCULATE and FILTER

Contexts in Calculated Columns

Introduction to Columns

Row Context in Calculated Columns

Filter Context in Calculated Columns

Relationships in Row Contexts

The RELATED function

The RELATEDTABLE function

Using RELATEDTABLE in a column

CALCULATED in row contexts

Row to Filter Context

Transformation Whiteboard

Exercise on row to filter contexts

Duplicates in the transformation

The LOOKUPVALUE function

Row Contexts in Measures

Introduction to Row Contexts

Row Contexts in Measures

Simple iterator in a measure

Row Context and a related table

Some more calculations exercise

Re-using measures

Implicit CALCULATE in a measure

An alternative approach using variables

Functions and FILTER

Introduction to DAX functions

Time Intelligence functions

Date (Dimension) tables

Using in-built TOTALYTD

Date modifier functions

Using date modifier DATESBETWEEN

Using date modifier DATESYTD

The magic behind Date Table functions

The case for simplicity – MAX or LASTDATE

Understanding the FILTER function

Contexts in the FILTER function

The filter context in functions

Naked column references

Simple filter expression

Comparing a value in the filter context

Adding another filter condition

The value of VALUES

Table Functions

Introduction to Table functions

Summarising tables

Using SUMMARIZE alone

The problem with SUMMARIZE

Using ADDCOLUMNS and SUMMARIZE

Using SUMMARIZECOLUMNS as a better alternative

A summary table in a measure

Practical DAX

Introduction to DAX calculations

Comparing functions – min/max and time intelligence

Debugging with DAX

Alternative patterns

The Quick Measure version

The KEEPFILTERS function

The TREATAS function

A few ORs or TREATAS

Using TREATAS and KEEPFILTERS

Using TREATAS for virtual relationships

DAX Tools

Use of third party tools

Introducing DAX Studio

What is DAX Studio

Download and Install DAX Studio

The DAX Studio Interface

How DAX Studio connects to the data

Viewing data model information

Creating a table query

Ordering a query result

A query returning a scalar

Defining a Measure

Declaring a variable

Incoming filter context

DAX Performance

Introduction to DAX Performance

The basics of the Vertipaq engine

Where is Vertipaq

A database of columns

Compression of a column

Identifying column compression

Understanding the TABLE_ID column

Power Query data statistics

Data Model Normalisation

Introduction to the Query Plan

Overview of the engines

The Formula Engine

The Storage Engine

Capturing a query

Special process to capture Power BI queries

Re-loading Power BI visuals

Understanding the query information from the capture

DAX Optimisation

Introduction to optimising DAX

Clear the Cache

Server Timings

Explaining the Server Timings

Steps to improve performance

Redefine a measure

The datacache

Optimisation by the Vertipaq engine

The CallBackDataID function

Reducing CallBackDataID instances

Optimising a Query in the Storage Engine

Viewing the Query Plan

Comparing two statistical functions

Extended Topics

Table Joins

Introduction to Table Joins

The Inner Join

A join on unrelated tables – TREATAS

The Outer Join

The Cross Join Using

GENERATE for Joins

Nested Row Context

Introduction to Nested Rows

How Nested Rows work

The EARLIER function

The EARLIEST function

Create a nested row in a column

How to avoid complex nesting expressions

Using variables to store values before nesting

Avoid iterators where possible

Further improve the efficiency of an expression

Ranking

Introduction to Ranking

Arguments in RANKX

Create a calculated column for ranking

Sort order of ranking

Definitions when ranking ties

Breaking Ties Manually

Creating a measure for ranking

Adjustments to make the RANKX measure work