Course Overview

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.

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.

Prerequisites

• • Delegates should ideally have a working knowledge of Power BI Desktop, commensurate with that attainable by attending the “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.
    

Course Content

• Introduction to DAX

  Introducing the DAX Expression Language
  

• Calculated Columns

  Adding Calculated Columns
  Properties of Calculated Columns

• DAX Measures

  Adding Measures
  Formatting DAX Code
  Measures that Use Other Measures
  Benefits of Measures

• Data Modelling 101

  Tabular Data Modelling Concepts
  Table Relationships
  Filters Across Multiple Tables
  Active & Inactive Relationships

• The CALCULATE Function

  Using the CALCULATE Function
  Evaluation Context
  Filter Operators
  Modifying the Filter Context

• The FILTER Function

  When Should You Use FILTER?
  Using the FILTER Function

• Working with Dates and Time Intelligence

  Date Functions in DAX
  Introduction to Time Intelligence
  Creating a Date Table
  Time Intelligence Functions
  Working with Weeks

• Conditional and Branching Logic

  Using the IF Function
  Checking for the Existence of Data
  The VALUES Function

• Iterator Functions

  The Purpose of Iterator Functions
  The SUMX Function
  Other Aggregate Iterator Functions
  Understanding Semi-Additive Calculations
  

• Complex Table Relationships

  Multiple Relationships Between the Same Two Tables
  The USERELATIONSHIP Function
  Disconnected Tables

• Custom Date Tables

  Introducing Custom Date Tables
  Creating Measures to Work with Custom Calendars

• Enhanced DAX Functionality

  Ranking Data
  Using DAX Variables
  Handling Errors in DAX