Teach on Udemy

Turn what you know into an opportunity and reach millions around the world.

Learn More

Your cart is empty.

Keep shopping

Power BI Master Class-Data Models and DAX Formulas

Name: Power BI Master Class-Data Models and DAX Formulas
Rating: 4.6 (8144 reviews)

Master DAX in Power BI - build data models, write measures and time intelligence that give you numbers you can trust

Created byIan Littlejohn

Last updated 9/2026

English

English [Auto],French [Auto],

What you'll learn

Write DAX measures with CALCULATE, ALL and ALLEXCEPT so your numbers respect the filters you intend and ignore the ones you do not
Build time intelligence measures for year-to-date, prior period and year-on-year comparisons
Use the iterator functions - SUMX, MAXX and RANKX - for calculations that work row by row
Create relationships between tables, and use USERELATIONSHIP when one relationship is not enough
Build virtual tables and cross-table calculations for questions a single table cannot answer
Segment customers and build scenario models using SWITCH and What If Parameters
Design a date master table and a star schema so the model stays fast as the data grows
Work through case studies on DirectQuery, Quick Measures, Performance Analyzer and the DAX Query View

Course content

10 sections • 56 lectures • 4h 11m total length

Introduction to the Course1:34
Meet your instructor and see what this master class covers: DAX calculated columns and measures, time intelligence, virtual tables, relationships, hierarchies and data groups.
About the Course1:23
How to get the best from the course: setting the video quality to full HD, changing the playback speed to suit you, and where to go for Udemy support and your certificate.
Download the training data files0:04
Download the four training files used through the course - the sales data, the date master, the employee master and the HR date master.
Power BI Desktop Configuration0:26
Written notes on configuring Power BI Desktop before you start, so your settings match the ones used in the lectures.
Power BI Desktop Setup2:57
Set up Power BI Desktop and check the options that matter before building a data model.
Overview of the Data Model6:20
An overview of the Power BI data model: tables, relationships and where calculated columns and measures live.
Basic Data Model4:46
Build the basic data model the course works with, ready for the DAX that follows.
Welcome to Udemy Roleplay0:36
What the Udemy role-play is: practice discussing a Power BI data model with an AI character, and how to get the most from the conversation.
Discuss Power BI data project

Introduction Calculated Columns1:08
What this section covers: DAX calculated columns - arithmetic, date fields, WEEKDAY and WEEKNUM, formatted date names, a date master table, IF and SWITCH.
Creating Calculated Columns9:35
Write a DAX calculated column that adds a new field to a table, evaluated row by row as the data loads.
Your turn: create calculated columns0:18
Your turn: create the calculated columns the brief asks for.
Creating Calculated Columns of Date Fields6:34
Write DAX calculated columns on a date field - pulling the year, month and day out for reporting.
Your turn: create date calculated columns0:11
Your turn: create calculated columns from the date fields.
Weekday and Weeknum Formulas3:38
Use the DAX WEEKDAY and WEEKNUM functions to add the day of the week and the week number to each row.
Formatting Date Names7:58
Use the DAX FORMAT function to turn month and weekday numbers into names, so reports read January and Monday.
Your turn: format the date names0:25
Your turn: add the formatted date name columns to the model.
Using a Date Master Table in the Data Model10:34
Add a date master table to the data model and relate it to your data, so every date based measure uses one calendar.
Your turn: add a date table to the model0:19
Your turn: add a date table to the data model and relate it to the sales data.
IF Function3:42
Use the DAX IF function in a calculated column to test a condition and categorize each row.
Switch Function5:27
Use the DAX SWITCH function to evaluate several conditions in one readable formula instead of nesting IFs.
Discuss DAX Calculated Columns in the Data Analysis project

Introduction to the Measures Section1:19
What this section covers: DAX measures - what they are, how they differ from calculated columns, CALCULATE, and ALL and ALLEXCEPT.
What is a Measure?5:51
Understand what a DAX measure is: a calculation evaluated at query time in the filter context of the visual, not stored row by row.
Creating Measures8:10
Write your first DAX measures and use them in a visual, with the formatting that makes them readable.
Your turn: write your first measures0:12
Your turn: write your first measures against the sales data.
Walkthrough: the measures exercise5:45
Walkthrough of the measures exercise: writing each measure and checking it in a visual.
Using the = Calculate Formula8:51
Use the DAX CALCULATE function to evaluate a measure with filters of your own applied - the most powerful function in DAX.
Your turn: measures with CALCULATE0:16
Your turn: write measures that use CALCULATE to apply their own filters.
The All and AllExcept Function8:47
Use the DAX ALL and ALLEXCEPT functions inside CALCULATE to ignore filters, the basis of percentage of total calculations.
Discuss the usage of Measures in the Data Analysis project

Time Intelligence: year-to-date and month-to-date11:19
Build year to date and month to date measures with the DAX time intelligence functions, driven by the date master table.
Time Intelligence: comparing to the same period last year8:14
Compare a period against the same period last year with the DAX time intelligence functions, and show the difference.
Time Intelligence: rolling periods and custom date ranges9:13
Build rolling period and custom date range measures - a moving window such as the last 12 months - with DAX.

Using the SUMX function5:35
Use the DAX SUMX function to calculate row by row across a table and total the result, instead of storing a calculated column.
Example of MAXX - Find the Highest Sales Day0:05
A worked MAXX example: find the highest sales day by totaling each day first, then taking the maximum.
Example MAXX Functions using Values function6:03
Use MAXX with the DAX VALUES function to build the list of days to evaluate before taking the highest.
RANKX Function7:26
Use the DAX RANKX function to rank customers or products by a measure, largest to smallest.
Customer Segmentation - SWITCH Function6:13
Use SWITCH as a measure to segment customers by their total sales, applying the bands at visual level.
What If Parameters7:10
Add a What If parameter so a slicer can feed a value into your DAX and the report responds to it.

Introduction to Virtual Tables0:40
What this section covers: virtual tables - tables built inside a DAX expression rather than stored in the model.
Creating Virtual Tables5:18
Build a virtual table inside a measure with SUMMARIZE, and use it as the basis of a calculation.
Customer Segmentation4:52
Segment customers with a virtual table, so the banding is worked out at query time rather than stored.
Your turn: build a virtual table0:36
Your turn: build a virtual table and use it in a measure.
Walkthrough: the virtual table exercise9:50
Walkthrough of the virtual table exercise: writing the SUMMARIZE expression and checking the measure in a visual.
Meeting to discuss the usage of virtual tables in the data analysis project

Introduction to the Relationships Section1:06
What this section covers: relationships between tables, calculating across them, and the USERELATIONSHIP function.
Download the Training Data Files0:02
Download the employee master and HR date master files used in the relationships section.
Relationships between Tables8:04
Create relationships between tables in the model view: match the key columns and set the cardinality and filter direction.
Relationship Calculations4:56
Write measures that calculate across a relationship, using fields from both of the related tables.
USERELATIONSHIP Function8:40
Use the DAX USERELATIONSHIP function to activate an inactive relationship inside a single measure - the way to report on a second date field.
Your turn: create relationships and calculations0:48
Your turn: create the relationships and the calculations that run across them.
Discuss creating Relationships in your data model

Introduction to Hierarchies0:52
What this section covers: hierarchies for drilling down, and data groups for bucketing values.
Creating Hierarchies5:33
Create a hierarchy in Power BI so a visual can drill down through the levels from one field.
Creating Data Groups6:06
Create a data group to bucket values - grouping items into bands or named sets without changing the source data.
Discuss using hierarchies and Data Groups in the data analysis project

Introduction to Case Studies1:16
What this section covers: three case studies - the Performance Analyzer, visual calculations and the DAX query view.
Performance Analyzer5:49
Use the Performance Analyzer to see how long each visual takes and which DAX query is behind it, then work out what to fix.
Visual Calculations7:45
Write a visual calculation - a calculation held inside the visual rather than as a measure on the model - and see when to prefer it.
DAX Query View9:46
Use the DAX query view to write and run queries against the model and see the results as a table.

Requirements

Power BI Desktop, downloaded free from Microsoft. It runs on Windows only - Power BI Desktop does not run natively on Mac.
No Power BI licence or subscription is needed. Everything in this course runs in Power BI Desktop on your own machine - you will not need a work email address or a Power BI Service account.
You should be able to load data into Power BI and build a basic report already. If you cannot, take Complete Introduction to Microsoft Power BI first.
Comfort with formulas in Excel helps, but DAX is taught from the beginning - no prior DAX experience is assumed.

Description

This course contains the use of artificial intelligence.

Every lesson in this course is written, created and recorded by me. AI is used only to help produce supporting images and written materials around the lessons.

Your report looks right and the numbers are wrong. The total does not match the sum of the rows, the percentage changes when somebody clicks a slicer, and last year's comparison quietly stops working in January.

That is a data model and DAX problem, and this course is about fixing it properly rather than working around it. You will build the model, write the measures, and understand well enough why a number comes out the way it does that you can debug the next one yourself.

COURSE CONTENT AND OUTCOME

Write DAX measures with CALCULATE, ALL and ALLEXCEPT so your numbers respect the filters you intend and ignore the ones you do not
Build time intelligence measures for year-to-date, prior period and year-on-year comparisons
Use the iterator functions - SUMX, MAXX and RANKX - for calculations that work row by row
Create relationships between tables, and use USERELATIONSHIP when one relationship is not enough
Build virtual tables and cross-table calculations for questions a single table cannot answer
Segment customers and build scenario models using SWITCH and What If Parameters
Design a date master table and a star schema so the model stays fast as the data grows
Work through case studies on DirectQuery, Quick Measures, Performance Analyzer and the DAX Query View

A NOTE ON COPILOT AND AI

You may be wondering where Copilot fits in. Copilot in Power BI runs on a Microsoft Fabric capacity, which is something your organisation buys and switches on - not something you can enable yourself. Most people learning Power BI do not have it, and will not have it for a while.

There is a second reason it matters less here than you might think. DAX is where reports go wrong quietly, and a wrong measure does not look wrong - it looks like a number. Whether you write it yourself or something writes it for you, you still have to be able to read it and say whether it is right. That is what this course teaches.

ABOUT THE TRAINER

I have been training business people to work with data since 2008, and publishing on Udemy since 2013. I now have 16 live courses with more than 400,000 students and more than 139,000 reviews, at an average rating of 4.6.

I teach Microsoft Power BI, Microsoft Excel, Copilot in Excel, Looker Studio and Amazon QuickSight. What makes my courses different is that I teach the analysis, not just the tool - every lesson starts with a business question someone actually asks, and shows you how to answer it with software you already have.

WHAT STUDENTS ARE SAYING

This course is great, it revealed to me functions that I never thought existed. I cannot wait to apply these in real time scenarios. Thanks Ian you are doing a great job.
Coming from Spotfire (after company decided PowerbI is cheaper) with its slightly different approach, this course helped to gain some traction with PowerBI
I felt quite comfortable using Power BI, but this course opened my eyes to many more possibilities the tool offers. I really appreciate the straightforward, no-nonsense and easy to follow teaching style from the instructor. Highly recommended to all who have been using Power BI and are ready for the next step.

The training data files, the practical activities and the case studies are all included. Enrol now and by the end you will be writing measures you can explain to somebody who asks where the number came from.

Who this course is for:

Power BI users whose reports work but whose numbers do not always agree - the total does not match the sum, the percentage changes when you add a slicer, and you are not sure why.
Analysts who can build a report but have never written a measure, and keep hitting the wall where drag-and-drop stops being enough.
Anyone who needs year-on-year, year-to-date and prior-period comparisons and has been faking them with calculated columns.
You should already be able to load data and build a basic report. If you cannot, take Complete Introduction to Microsoft Power BI first - it starts at installation.
This is the model and calculation half of the Power BI Master Class. Its other half, Power BI Master Class - Dashboards and Power BI Service, covers visuals, interactive reports and publishing to your team. Take them in either order; this one is the deeper of the two.

Power BI Master Class-Data Models and DAX Formulas

What you'll learn

Explore related topics

Course content

Introduction to the Course9 lectures • 18min

Creating Calculated Columns13 lectures • 50min

Creating Power BI Measures9 lectures • 39min

Time Intelligence Functions3 lectures • 29min

Additional Measure Calculations6 lectures • 33min

Virtual Tables6 lectures • 21min

Creating Relationships in Power BI7 lectures • 24min

Hierarchies and Data Groups4 lectures • 13min

Case Studies4 lectures • 25min

Conclusion to the Course2 lectures • 1min

Requirements

Description

Who this course is for: