Teach on Udemy

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

Learn More

Your cart is empty.

Keep shopping

Complete Introduction to Excel Power Pivot and DAX

Name: Complete Introduction to Excel Power Pivot and DAX
Rating: 4.5 (2780 reviews)

Build Excel data models with millions of rows, write DAX measures and KPIs, and answer business questions fast.

Created byIan Littlejohn

Last updated 9/2026

English

German [Auto],English [Auto],

What you'll learn

Load millions of rows into the Excel Data Model, then browse, filter and sort them inside Excel
Create relationships between tables so one PivotTable can report across your whole dataset
Write DAX calculated columns for dates, week days and categories using IF and SWITCH
Build DAX measures with CALCULATE, ALL and ALLEXCEPT so a number is filtered by exactly what you intend
Compare one period against another with Time Intelligence measures
Rank and segment your customers using RANKX, SUMX and SWITCH
Add KPIs, sets and hierarchies to the model, and build a four-chart dashboard in the practical activities

Course content

11 sections • 58 lectures • 3h 43m total length

Introduction to the Course1:47
Meet your instructor and see why Power Pivot matters: a database inside Excel, relationships between tables, millions of rows, and the DAX formula language.
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 Course Training Files0:04
Download the sales data workbook, date master and employee master files used throughout the course so you can build the Power Pivot model alongside the lectures.

Overview of Excel Power Pivot0:41
What this section covers: what Power Pivot is, activating it in Excel, loading data into the data model, and browsing, filtering and sorting that data.
Understanding Power Pivot2:05
Understand what Power Pivot is: a database inside Excel that holds millions of rows across related tables, removing the worksheet row limit and the need for VLOOKUPs between tables.
Activating Excel Power Pivot2:03
Activate the Power Pivot add-in from File, Options, Add-ins, Manage COM Add-ins to add the Power Pivot tab to the Excel ribbon.
Loading Data into the Data Model5:47
Load the course training workbook into Power Pivot from Manage Data Model, the first of several ways to get data into the data model.
Browse, Filter and Sort5:36
Work with a loaded table in the Power Pivot window: rename the table, then browse, sort and filter the rows before you analyze them.
Your turn: load data into the model0:21
Your turn: load the training data file into the Power Pivot data model and rename the table ready for analysis.
Welcome to the Udemy Roleplay!0:36
What the Udemy role-play is: practice discussing an Excel Power Pivot project with an AI character, and how to get the most from the conversation.
Discuss Excel Power Pivot project

Introduction to Analyzing Data with Pivot Tables0:31
What this section covers: building PivotTables on the Power Pivot data model to summarize and aggregate your data, with methods of aggregation, slicers and PivotCharts.
Creating Pivot Tables7:39
Create a PivotTable directly from the Power Pivot window and lay out rows, columns and values to present the data held in the data model.
Methods of Aggregation5:34
Change how a PivotTable summarizes a field - Sum, Average, Count, Max, Min and Distinct Count - and use the same field more than once with different aggregations.
Slicers and Filtering Data with Pivot Tables7:14
Filter a PivotTable built on the data model: clear it with Clear All, then add slicers and filters to show only the results you want.
Two Pivot Table and Chart5:45
Add a second PivotTable and a PivotChart to the same sheet from the Power Pivot menu, and connect a slicer so both respond together.
Your turn: create pivot tables0:17
Your turn: build the sales by country report - total, average, highest and lowest sale and the number of categories sold in each country.
Walkthrough: the pivot tables exercise5:33
Walkthrough of the Pivot Tables exercise: building the sales by country report with total, average, highest and lowest sale and a count of categories sold.
Discuss the usage of Pivot Tables with Power Pivot

Introduction to Calculated Columns1:09
What this section covers: adding new fields to a Power Pivot table with DAX formulas - arithmetic, date parts, a date master table, IF and SWITCH.
Creating a Calculated Column5:17
Add a calculated column in Power Pivot that multiplies list price by order quantity to create a sales field, using familiar Excel style operators.
Year Month Day4:54
Split a sales date into separate Year, Month and Day calculated columns with the DAX YEAR, MONTH and DAY functions so you can analyze by each part.
Weeknum and WeekDay2:57
Add WEEKDAY and WEEKNUM calculated columns in DAX, using the WEEKDAY return type argument to set Monday rather than Sunday as day one of the week.
Formatting Month and Week Day Names6:06
Turn month and weekday numbers into names with the DAX FORMAT function, so PivotTables show January and Monday rather than 1.
Your turn: add calculated columns0:30
Your turn: add calculated columns to the data model, including the date part fields and a name field, using DAX formulas.
Creating a Date Table9:20
Build a date master table once, then relate it to your data so every model gets year, month, weekday and month name without rebuilding those columns.
IF Function3:59
Use the DAX IF function in a calculated column to test a condition and categorize each row, for example flagging sales over or under $1,000.
SWITCH Function5:45
Use SWITCH with TRUE in a calculated column to evaluate several conditions in one readable formula instead of nesting IF statements.
Discuss DAX Calculated Columns in the Data Analysis project

Introduction to Measures1:11
What this section covers: measures - calculations held against the data model and evaluated when the PivotTable needs them - including aggregations, CALCULATE, ALL and ALLEXCEPT.
Explanation of Measures3:30
Understand the difference between calculated columns and measures in Power Pivot: a calculated column is evaluated row by row inside a table, a measure is evaluated in the filter context of the PivotTable.
Aggregations9:27
Write your first DAX measures - SUM, AVERAGE, COUNT and the rest - and use them in a PivotTable in place of a summarized field.
Your turn: add measures0:10
Your turn: create the measures the sales manager needs, including average sale value, and format them in the PivotTable.
Walkthrough: the measures exercise6:28
Walkthrough of the Measures exercise: writing the average sale measure and the rest into the PivotTable and formatting the results.
The =Calculate Function6:22
Use the DAX CALCULATE function, the most powerful function in DAX, to evaluate a measure with filters of your own applied.
Your turn: write CALCULATE measures0:16
Your turn: use CALCULATE to write measures that filter to a single business segment, such as sales of clothing only.
Walkthrough: the CALCULATE exercise4:17
Walkthrough of the CALCULATE exercise: passing a business segment filter into CALCULATE alongside the existing total sales measure.
The ALL and ALLEXCEPT Functions9:50
Use the DAX ALL and ALLEXCEPT functions inside CALCULATE to ignore PivotTable filters, the basis of percentage of total calculations.
Discuss the usage of Measures in the Data Analysis project

Date Ranges and Rolling Periods - DATESBETWEEN and DATESINPERIOD7:49
Create rolling period measures with the DAX DATESBETWEEN and DATESINPERIOD functions to total sales over a moving window such as the last 12 months.
Month on Month and Year on Year - PREVIOUSMONTH and SAMEPERIODLASTYEAR7:37
Build a management report with the DAX PREVIOUSMONTH and SAMEPERIODLASTYEAR functions: totals by year and month, the previous month, and the difference between them.
Running Totals - TOTALYTD, TOTALQTD, TOTALMTD and Filter Context8:16
Build running totals with the DAX TOTALYTD, TOTALQTD and TOTALMTD functions, and see how filter context from the date master drives what each one returns.

Using SUMX Functions6:17
Use SUMX to calculate list price times quantity at query time instead of storing a calculated column, keeping the data model smaller and faster.
Your turn: write a SUMX measure0:05
Your turn: use MAXX to total sales by day and return the highest day of sales, rather than the highest single transaction.
Walkthrough: the SUMX exercise3:44
Walkthrough of the SUMX exercise: aggregating sales to day level inside MAXX so the measure returns the best day rather than the best transaction.
RankX Function - Ranking Customers5:47
Use the DAX RANKX function to rank customers by their total sales rather than leaving them in alphabetical order.
Customer Segmentation - The Switch Function3:13
Use SWITCH as a measure to segment customers by their total sales, applying the conditions at PivotTable level rather than row by row.
Discuss the usage of more advanced functions in the Sales data model

Introduction to Case Studies0:58
What this section covers: two case studies - loading a text file of more than a million rows into the data model with Power Query, and using Power Query to build summary tables.
Download the Case Study Data Files0:07
Download the large text file used in the case study section so you can load more than a million rows into the data model yourself.
Case Study - Loading Data from Power Query6:50
Load a CSV or TXT file well beyond Excel's million row limit straight into the Power Pivot data model with Power Query.
Creating Summary Tables4:38
Use Power Query to build summary tables from a very large source table, so the model carries the aggregates you need rather than every transaction.
Discuss the usage of using Power Query to Load Data and Create Summary Tables

Introduction to Relationships0:53
What this section covers: relating an employee master table to workshop training data in Power Pivot, and calculating across the join.
Download Relationships Data Files0:03
Download the employee master and workshop files used in the Relationships section so you can build the related tables yourself.
Creating Relationships5:35
Load a second table into Power Pivot and create the relationship between the two tables so a PivotTable can use fields from both.
Relationship Calculations4:15
Use the DAX RELATED function to bring a field from a related table into the data view, so you can calculate across the join inside the model.
Your turn: build the employee four-chart dashboard0:20
Your turn: load the employee master and workshop sheets into Power Pivot, relate them, and build a four-chart dashboard of the training data.
Walkthrough: the employee dashboard exercise7:55
Walkthrough of the Employee Four-Chart Dashboard exercise: loading both sheets, creating the relationship, and laying out the four charts.
Discuss creating Relationships in your data model

Introduction to KPIs and Sets0:23
What this section covers: sets, hierarchies and KPIs - the last capabilities to add to your Power Pivot data model.
Creating Sets5:12
Create a set in Power Pivot: a saved, filtered list of items that behaves like a field you can drop into any PivotTable.
Creating Hierarchies4:08
Create a hierarchy in the Power Pivot diagram view, such as region to country, so a PivotTable can drill down through the levels from one field.
Creating KPIs4:24
Create a KPI in Power Pivot: pick the measure, set the target and the status thresholds, and show the indicator icons in a PivotTable.
Discuss using KPIs, Sets and Hierarchies in the Sales data model

Requirements

Excel for Windows: Microsoft 365, Excel 2024, Excel 2021 or Excel 2019. On Excel 2016, Power Pivot is only in Professional Plus.
Power Pivot is included in those versions - there is no separate Power Pivot licence to buy. It is an add-in you switch on under File > Options > Add-ins, and the course shows you how in the first section.
Power Pivot is not available in Excel for Mac or Excel on the web. You need the Windows desktop version of Excel.
You should be comfortable working with data in Excel and building a basic PivotTable. No DAX or data modeling experience is needed.

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 sales are in one table, your customers in another and your calendar in a third - and the PivotTable will only look at one of them.

That is the wall every Excel user eventually hits, and Power Pivot is the way through it. It loads millions of rows into Excel, joins your tables with relationships, and lets you write measures that stay correct however anyone slices them. There is nothing to buy - it is already built into Excel for Windows, switched off by default, and the first section shows you how to turn it on.

I teach the analysis, not the tool. Every section starts with a question a manager actually asks - which customers are growing, how does this quarter compare with last year, who are my top ten accounts - and then shows you the Power Pivot method that answers it.

WHAT YOU WILL LEARN

Loading data into the Excel Data Model, then browsing, filtering and sorting it
Building PivotTables and PivotCharts on the model, with slicers and multi-chart layouts
Calculated columns: year, month, week day, a proper date table, IF and SWITCH
Measures: aggregations, CALCULATE, ALL and ALLEXCEPT, so a number is filtered by exactly what you intend
Time Intelligence measures for comparing one period against another
SUMX, RANKX for ranking your customers, and SWITCH for customer segmentation
Relationships between tables, and calculations that cross them
KPIs, sets and hierarchies in the data model
Two case studies, including loading from Power Query and building summary tables

HOW IT IS TAUGHT

Short lessons of four to ten minutes in HD, with all the training data files provided. Every section has a Practical Activity - a written brief and the data - followed by a walkthrough of the completed answer, so you build the model yourself rather than watch me build it. There is a four-chart dashboard to construct in the Relationships section.

A NOTE ON COPILOT

Copilot in Excel is very good at answering a question about one table you already have. It does not build a data model, join tables or write DAX. That is what this course teaches, and it is the part that does not go away - a model is the thing an AI assistant needs before it can give you a trustworthy answer about more than one table.

ABOUT THE TRAINER

I am a Udemy Instructor Partner. I have been training business people to work with data since 2008 and publishing on Udemy since 2013. Across 16 courses I have taught more than 398,000 learners and hold a 4.6 average from more than 139,000 reviews.

This course is rated 4.6 from more than 2,700 ratings. I specialise in training business users in Microsoft Excel, Copilot in Excel, Power Query, Microsoft Power BI, Looker Studio and Amazon QuickSight.

WHERE THIS COURSE SITS

This course assumes you can already build a basic PivotTable. If you cannot, take Complete Introduction to Excel Pivot Tables first. If your problem is getting messy data into Excel rather than modeling it once it is there, that is Complete Introduction to Excel Power Query. And Power Pivot uses DAX, the same formula language as Power BI - so this is also the least expensive way to learn DAX before you move across.

Ready to get past the one-table limit? Let's get started.

Who this course is for:

Excel users who can build a PivotTable but have hit the limit of one flat table, and need to report across several tables at once
Analysts working with more rows than a worksheet will hold, who need Excel to handle the volume without slowing to a crawl
Report builders who rebuild the same monthly summary by hand and want measures that recalculate themselves
Anyone who wants to learn DAX inside Excel before moving to Power BI - it is the same formula language in both

Complete Introduction to Excel Power Pivot and DAX

What you'll learn

Explore related topics

Course content

Introduction3 lectures • 3min

Power Pivot and the Data Model8 lectures • 17min

Power Pivot and Pivot Tables8 lectures • 33min

Calculated Columns10 lectures • 40min

Measures in the Data Model10 lectures • 42min

Time Intelligence Measures3 lectures • 24min

Additional Measures6 lectures • 19min

Case Studies5 lectures • 13min

Relationships7 lectures • 19min

Sets and KPIs5 lectures • 14min

Requirements

Description

Who this course is for: