Decoding DAX

A review of DAX and its most basic functions; SUM, MIN, MAX. In this module, you will get familiar with the dataset that we will be using throughout the course and learn a few tricks for keeping your DAX code easy to read.

Topics:

What is DAX?

DAX Syntax

DAX Functions

DAX Operators

Data Types

Common DAX Functions

Labs:

Unit Price Analysis

Reporting Sales

Learn the differences between calculated columns and measures, where to use each, when they are calculated, and how they are impacted by slicers and filters in your report. Learn how to leverage relationships in your data model to create calculated columns using data from multiple tables.

Topics

Calculated Columns

Implicit vs Explicit Measures

Naming Convention

Using measures and columns

Labs:

Sale Type by City

Discounts

Now that you understand columns vs measures, start to debunk the concept of Evaluation Contexts in DAX and understand why measures behave the way they do.

Topics

Introduction to evaluation contexts

Filter Context

Row Context

Labs:

Average Sales

CALCULATE is a powerful DAX function that is often misunderstood. Learn the basic syntax of CALCULATE and use it to calculate ratios.

Topics

Introduction to CALCULATE

CALCULATE modifiers

Labs:

Ratios and Percentages

In this module, we will continue working with the CALCULATE function and explore its power of context transition. Learn what this means for your calculated columns.

Topics

Introduction to Context transition

Automatic CALCULATE in measures

Equivalent Filter context

Labs:

Manipulating Evaluations Contexts

Iterators create a virtual calculated column. This can help reduce the amount of DAX you need to write to get your desired result. In this module, we’ll explore common iterators and see the importance of understanding that evaluation context when working with these virtual calculated columns.

Topics

Introduction to Iterators

Useful iterators

Aggregating Expressions

Labs:

Sales per Working Day

Now that you’re familiar with Iterators, we can leverage the power of table filter functions to narrow in on exactly the information we need.

Topics

Introduction to Table Functions

FILTER Function

VALUES, DISTINCT Functions

Labs:

Ranking Stores by Profit

Finding above average Customers

Building the right data model is crucial to the success of any report. Learn how DAX uses the relationships in your data model to calculate the results.

Topics

Advanced evaluation contexts

Row context and relationships

Filter context and relationships

Filtering many columns

Crossfiltering

Expanded tables

USERELATIONSHIP

Labs:

Total Shipments vs Total Orders

Variables can help keep your DAX code tidy and optimized, but more importantly they can provide context transition within measures. In this module, we will learn how variables are calculated and when to use them.

Topics

Variables syntax

Calculation of variables

Variables for optimization

Variables for context transition

EARLIER function vs variables

Labs:

First purchase amount

Having a robust date table is key to any time intelligence calculations. Learn the few simple steps you need to take to ensure your time intelligence functions calculate properly.

Topics

Date table properties

CALENDAR vs CALENDARAUTO vs import

Multiple dates

Labs:

Mark as Date Table

Set sorting options

Don’t break out the calculator just yet- DAX time intelligence functions enable you to easily make comparisons for Year on Year, Month on Month, Year to Date, 12 Month Rolling average and more. In this module, we will learn the formula for success with DAX time intelligence.

Topics

Introduction to Time Intelligence

Common Time Intelligence Functions

Time Intelligence DAX formula for success

Labs:

Year to Date Sales Percent Change (from previous year)

Rolling 12 Month Average

Use Power BI built-in DAX parameters and create your own parameter tables to perform What-if analysis on your data, create custom filters, and change the display value or measure.

Topics

Power BI DAX Parameter

GENERATESERIES, SELECTEDVALUE

Single Value Slicers

Custom Parameters

Labs:

What-if Analysis

Sales Quantity vs Sales Amount

Today, Tomorrow, Yesterday slicer

Now that know all the common DAX functions and their evaluation contexts, how do you optimize your code? In this module, we will look at some tools for determining the performance of your DAX as well as some tips to help improve that performance.

Topics

Performance analyzer

Variables

Naming conventions

Labs:

Percent growth calculated two ways