Professional Training

Pre-requisites

Participants should have at least Intermediate Excel and database user skills. They need:
An understanding of basic data modelling.
Familiarity with using formula syntax
Experience using data models in Power Pivot or Power BI - having attended either our 1-day or 2 day Power BI Course
Familiarity with Power BI, Excel and Database models

Who Should Attend

This course has been designed as an add-on to the standard Power BI courses for users who need to learn about advanced report generation. It covers topics and areas of the DAX language that are not in the standard 1 or 2 day courses. Attendees need to be familiar with generating reports in Power BI.

Learning Objectives

DAX is useful to different people, depending on their focus. The course aims to assist:
Business Intelligence (BI) professionals and Power Users to implement DAX code in BI solutions of any size
Casual Power BI users to author some DAX formulae in their self-service BI models
Excel users to leverage DAX to author Power Pivot data models
At the end of this course participants will have a detailed understanding of the DAX Language.

What is DAX

Understanding the data model.
Understanding the direction of a relationship.
DAX for Excel users.
Cells versus tables.

Introducing DAX

Understanding DAX calculations.
DAX data types.
DAX operators.
Understanding calculated columns and measures.
Calculated columns.
Measures.
Variables.
Handling errors in DAX expressions.
Conversion errors.
Arithmetical operations errors.
Intercepting errors.
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

DAX Calculated Columns

Types of Calculations
New Columns
Naming Columns
Concatenating Column Contents
Tweaking Text
Simple Calculations
Math Operators
Rounding Values
Calculating Across Tables
Choosing the Correct Table for Linked Calculations
Cascading Column Calculations
Refreshing Data
Using Functions in New Columns
Safe Division
Counting Reference Elements
Statistical Functions
Applying a Specific Format to a Calculation
Simple Logic: the IF( ) Function
Exception Indicators
Flagging Data
Nested IF() Functions
Creating Custom Groups Using Multiple Nested IF() Statements
Multiline Formulas
Complex Logic
Formatting Logical Results

DAX Measures

Basic Aggregations in Measures
Using Multiple Measures
Cross-Table Measures
More Advanced Aggregations
Filter Context
Row Context
Query Context
Filter Context
Filtering Data in Measures
Simple Filters
Text Filters
Numeric Filters
More Complex Filters
Multiple Criteria in Filters
Using Multiple Filters
Calculating Percentages of Totals
A Simple Percentage
Removing Multiple Filter Elements
Visual Totals
The ALLEXCEPT() Function
Filtering on Measures
Displaying Rank
Calculation Options

Time Intelligence

Simple Date Calculations
Date and Time Formatting
Calculating the Age of Cars Sold
Calculating the Difference Between Two Dates
Adding Time Intelligence to a Data Model
Creating and Applying a Date Table
Creating the Date Table
Adding Sort By Columns to the Date Table
Date Table Techniques
Adding the Date Table to the Data Model
Applying Time Intelligence
YearToDate, QuarterToDate, and MonthToDate Calculations
Analyze Data As a Ratio over Time
Comparing a Metric with the Result from a Range of Dates
Comparisons with Previous Time Periods
Calculating Sales for the Previous Year
Comparison with a Parallel Period in Time
Comparing Data from Previous Years
Comparing with the Same Date Period from a Different Quarter, Month, or Year
Rolling Aggregations over a Period of Time