Mastering DAX

Duration

3,00 day(s)

Language(s) of service

EN FR

Next session

Who is organizing this training?

We enhance both individual development and efficiency by sharing the IT expertise and managerial experience of our instructors. We offer over 300 courses in French and English at our center in Capellen. These courses focus on infrastructure, development, project management, governance, and soft skills.

Prerequisites

This course is intended for people who have a background in Business Intelligence and have already built data models in Power BI or Analysis Services. U2U also offers a 3-day course Analyzing your data with Power BI for Business Users targeted at people without the skills to build a Semantic Model.

Goals

Power BI is a suite of business analytics tools to analyze data and share insights. DAX (short for Data Analysis Expressions) is a language used in Power BI and Analysis Services Tabular Models to create derived calculations such as year-to-date, percentage of total, etc. In this course you will learn how to use DAX to create measures and other derived calculations. You will also learn to use DAX as a query language.

An Introduction to DAX

This chapter provides an introduction to the DAX language. Students will learn the different use cases of the DAX language.

DAX Use Cases
Understanding Calculated Columns and Measures
Building Tables with DAX
DAX as a Query Language
DAX for Row-level Security
DAX Patterns
LAB: Creating Calculated Columns, Tables and Measures in DAX

Using the DAX Query View

DAX Query View is a feature in Power BI that allows developers to write, run, and analyze DAX queries for deeper insights into their data models. In this module, you will learn how to use DAX Query View in Power BI Desktop and the Power BI Service, along with an introduction to writing DAX statements and exploring key functions.

Using the DAX Query View in Power BI Desktop
Using the DAX Query View in Power BI Service
Writing DAX Statements
Important DAX Functions
Using INFO Functions to Query Metadata
LAB: Running and Profiling Queries in Power BI Desktop

DAX Expressions

When writing scalar expressions, a good understanding of DAX data types, operators and functions is needed.

DAX Data Types
Mathematical, Boolean and String Operators
The Most Important Scalar Functions in DAX
Conditional Functions
Date and Time Functions
LAB: Evaluating and Writing DAX Expressions

Getting Started with Measures

Learn how to define measures in DAX, understand the difference between row and filter context, and work with common aggregations. You will also see how to organize your model with measure tables and distinguish between implicit and explicit measures.

Row Context vs Filter Context
Building Measures
Aggregation Functions
Defining Measure Tables
Implicit vs Explicit Measures
LAB: Creating Measures and Measure Tables

Advanced Measure Design with CALCULATE

The CALCULATE function is at the heart of advanced DAX. Discover how the CALCULATE function changes filter context to enable advanced calculations. You will also explore key filter functions such as FILTER, ALL, VALUES, and DISTINCT to build more powerful measures.

The CALCULATE Function
Defining Table Scopes
Using the FILTER, ALL, VALUES and DISTINCT Functions
LAB: Using CALCULATE

Time Intelligence with DAX

Nearly all analysis happens against different time periods. DAX includes time intelligence functions that enable you to manipulate data using time periods, and then build and compare calculations over those periods.
Common challenges such as year-to-date, growth calculations, moving averages, etc. are covered in this module.

Creating Calendar Tables using CALENDAR and CALENDARAUTO
Working with Multiple Date Tables
Marking Tables as Date Tables
Using the DATEADD and PARALLELPERIOD Functions
Computing YTD, QTD and MTD Totals
Comparing Data over Time using DAX
Calculating Rolling Totals
Calculating Moving Averages
Creating Time Intelligence Measures using Bravo
LAB: Implementing Time Intelligence

Working with Calculation Groups

Often measures such as a year-to-date (YTD) calculation have to be repeated for multiple base measures. Calculation groups provide a template with which these repetitive measures can be easily created and maintained.

The Need for Calculation Groups
Creating Calculation Groups
Format Strings in Calculation Groups
Excluding Measures from Calculation Groups
Combining Calculation Groups with Field Parameters for Dynamic UX
LAB: Implementing a Time Intelligence Calculation Group

Measures: Iterators and Variables

When you need to iterate through data, iterators such as SUMX become useful. This chapter discusses how to work with iterators, how to apply context transitions within iterators and how to use variables to increase readability, performance and expressive power.

The SUMX Aggregation Function
Linking Tables with RELATED and RELATEDTABLE
More Iterators: MAXX, RANKX, CONCATENATEX ...
Using Variables to Increase Readability and Performance
Context Transition and its Pitfalls
LAB: Using Iterators in Measures

Inspecting and Controlling the Execution Context

Often a measure only makes sense in a certain context (e.g. only relevant at the month level, but not the year level). In this chapter DAX functions are covered which allow you to inspect the current context such that you
can protect your measures. A typical use case is calculating percentages of parent totals.