Mastering DAX

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 DAX Query View in Power BI Desktop
• Using 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 is performed across different time periods, and DAX provides time intelligence functions that allow you to manipulate and compare data over those periods. You can also extend your model with custom calendars, such as 13-month, lunar, or ISO-454 calendars, while addressing common scenarios like year-to-date calculations, growth analysis, and moving averages.

• 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
• Calculating Moving Averages
• Calendar-based Time Intelligence
• ISO-454, 13-month, Lunar and Weekly Calendars
• Fine-grained control for varying month lengths
• 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

Visual Calculations in Power BI

Visual Calculations let you create calculations directly within visuals, without adding measures to the data model. They simplify common analytical scenarios, can improve performance, and enhance readability by shifting certain logic from the semantic model to the visual layer.

• Visual Calculations vs DAX Measures
• Creating Visual Calculations in Power BI visuals
• Hierarchy Handling, Running Sums, Current vs Previous, ...
• Performance Gain of Visual Calculations
• Using Visual Calculations for Conditional Formatting
• Combining Visual Calculations, Parameters and Calculation Groups
• LAB: Creating and Using Visual Calculations

User-Defined Functions (UDFs) in DAX

User-Defined Functions (UDFs) allow you to encapsulate reusable DAX logic into named functions. They reduce repetition, improve maintainability, and help centralize complex business logic across your semantic model.

• Differences between UDFs and Measures
• Creating UDFs using the DAX Query View
• Defining parameters and return types
• Value types vs Expression types
• Scalar vs Table-returning UDFs
• Model-dependent vs Model-independent UDFs
• Calling UDFs in Measures and Calculated Columns
• Using UDFs with Visual Calculations and Calculation Groups
• LAB: Creating and Applying User-Defined Functions

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
• MAXX, AVERAGEX, CONCATENATEX, ...
• RANKX vs RANK
• 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.

• HASONEVALUE, ISFILTERED, ISINSCOPE, SELECTEDVALUE, ...
• Handling Hierarchies
• Calculating Ratios to Parent Subtotals
• Raising Errors
• LAB: Inspecting Execution Context

Advanced Context Modifiers in DAX

This chapter explores advanced DAX context modifiers that go beyond standard boolean and table filter expressions. You will learn how functions like USERELATIONSHIP, CROSSFILTER and TREATAS influence filter context and evaluation behavior, enabling more flexible and powerful calculations.

• USERELATIONSHIP, CROSSFILTER, ALLEXCEPT, KEEPFILTERS, ...
• TREATAS
• CALCULATETABLE
• LAB: Diving into Advanced Context Modifiers

Expanded Tables: The Hidden Engine of DAX

This chapter explains how expanded tables work behind the scenes in DAX and how they affect data propagation across relationships. You will learn how expanded tables influence filter context, enable interactions between related tables, and support more advanced calculation patterns in your model.

• Understanding Expanded Tables
• How Expanded Tables propagate filters through relationships
• Impact of Expanded Tables on CALCULATE and filter context
• Using Expanded Tables in Measures
• LAB: Working with Expanded Tables

Interacting with Power BI MCP Servers

MCP can help improve and maintain Power BI models using natural language, enabling bulk changes - such as renaming measures, adding descriptions, or adjusting formatting - through simple instructions instead of manual, repetitive work. In this module you will learn how to work with PBI MCP Servers.

• Overview: AI features, Licenses & Use Cases
• How to Prepare Power BI Models for AI
• Interacting with Power BI MCP Servers