Audience
This course is aimed at existing users of Power BI, Excel Power Pivot, or SQL Server Analysis Services (SSAS) Tabular, who need to take their data analysis skills to the next level.
Prerequisites
Delegates should ideally have a working knowledge of Power BI Desktop, commensurate with that attainable by attending the 3-day Power BI Desktop: Complete course, or similar.
Alternatively, prior experience of Power Pivot, SQL Server Analysis Services (SSAS) Tabular, or similar tool that works with the tabular model, should prove suitable.
No previous knowledge of DAX is assumed.
Duration
2 days. Hands on.
Course Objectives
During this course you will learn how to mine and expose hidden information with the DAX (Data Analysis eXpressions) language. Common business scenarios are covered, highlighting useful DAX patterns that can be implemented and adapted to solve a variety of simple and complex calculations. The theory behind the concepts of DAX will be emphasised through practical examples.
Little, or no, previous knowledge of DAX is assumed. The training is designed from the ground up to introduce delegates to this new and powerful technology, and ultimately provide attendees with a more intimate knowledge of DAX. There is a linear progression throughout the training from the initial introductory material through to more intermediate and advanced topics. There is an emphasis on practical applications of DAX to enable delegates to be productive right from the word go after attending this course.
You will find that this is a comprehensive course for this level, with plenty of illustrated examples and augmented with practical hands-on exercises.
Upon successful completion of this course, students will be able to:
- Create and use calculated columns
- Create and use measures
- Understand the key concepts of tabular data modelling
- Understand evaluation contexts
- Work with the CALCULATE function
- Work with the FILTER function
- Work with date functions
- Understand time intelligence in DAX
- Make decisions in DAX expressions
- Understand iterator functions
- Work with complex table relationships
- Create and use custom calendars
- Create and use DAX variables
- Gracefully handle errors in DAX
Course Content
1. Introduction to DAX
Course Overview
Introducing the DAX Expression Language
2. Calculated Columns
Adding Calculated Columns
Properties of Calculated Columns
3. DAX Measures
Adding Measures
Formatting DAX Code
Measures that Use Other Measures
Benefits of Measures
4. Data Modelling 101
Tabular Data Modelling Concepts
Table Relationships
Filters Across Multiple Tables
Active & Inactive Relationships
5. The CALCULATE Function
Using the CALCULATE Function
Evaluation Context
Filter Operators
Modifying the Filter Context
6. The FILTER Function
When Should You Use FILTER?
Using the FILTER Function
7. Working with Dates and Time Intelligence
Date Functions in DAX
Introduction to Time Intelligence
Creating a Date Table
Time Intelligence Functions
Working with Weeks
8. Conditional and Branching Logic
Using the IF Function
Checking for the Existence of Data
The VALUES Function
9. Iterator Functions
The Purpose of Iterator Functions
The SUMX Function
Other Aggregate Iterator Functions
Understanding Semi-Additive Calculations
10. Complex Table Relationships
Multiple Relationships Between the Same Two Tables
The USERELATIONSHIP Function
Disconnected Tables
11. Custom Date Tables
Introducing Custom Date Tables
Creating Measures to Work with Custom Calendars
12. Enhanced DAX Functionality
Ranking Data
Using DAX Variables
Handling Errors in DAX