DAX Power Pivot 10 Easy Steps for Advanced Users
4.5 (92 ratings)
Course Ratings are calculated from individual students’ ratings and a variety of other signals, like age of rating and reliability, to ensure that they reflect course quality fairly and accurately.
719 students enrolled

DAX Power Pivot 10 Easy Steps for Advanced Users

Learn advanced Data Modeling techniques, Variables and Budget Patterns
4.5 (92 ratings)
Course Ratings are calculated from individual students’ ratings and a variety of other signals, like age of rating and reliability, to ensure that they reflect course quality fairly and accurately.
719 students enrolled
Created by Gilly Dow
Last updated 6/2019
English
English [Auto-generated]
Current price: $132.99 Original price: $189.99 Discount: 30% off
5 hours left at this price!
30-Day Money-Back Guarantee
This course includes
  • 9 hours on-demand video
  • 1 downloadable resource
  • Full lifetime access
  • Access on mobile and TV
  • Certificate of Completion
Training 5 or more people?

Get your team access to 4,000+ top Udemy courses anytime, anywhere.

Try Udemy for Business
What you'll learn
  • Work with Multiple Data Tables
  • Build complex calculations with Variables
  • Gain Insights with RANKX() & TOPN()
  • Work with Differing Granularities
  • Use Budget Patterns
  • Budget vs Actuals
Requirements
  • Power Pivot must be enabled and visible as a Ribbon Tab
  • You know how to create a Data Model
  • You recognise the term Initial Filter Context
  • You understand CALCULATE()
  • IMPORTANT: You need a version of Excel that is compatible with Power Pivot (Excel 2013/2016/2019 Standalone, Office 365, Enterprise E3/E5, Office Professional 2016, etc.)
  • This course is designed for PC users (Power Pivot is currently unavailable with Excel for Mac)
Description

Are you ready to take your DAX skills to the next level? My new DAX Power Pivot – 10 Easy Steps for Advanced Users follows on from the my previous "10 Easy Steps" series to enable you to increase the complexity of your data models and progress to creating and understanding more complex calculations.

I have created this course specifically for those students who have taken my Beginners and Intermediates courses where you have learned the fundamentals necessary to transition into more advanced concepts enabling you to build bigger data models and create more complex calculations.

By helping you to understand evaluation context will help you "think" like DAX. When you have this understanding you’ll be in a better position to troubleshoot when things don’t go according to plan.

Course Contents

  • Warm Up

  • Variables

  • Row & Filter Context

  • Static Segmentation

  • Multiple Data Tables

  • RANKX() & TOPN()

  • Different Granularities

  • Budget vs Actuals

  • Budget Patterns (Single Year)

  • Budget Patterns (Multiple Years)

  • Budgets (Monthly)


I provide examples that we work through together. Then it’s over to you with practice exercises to help you embed and consolidate your skills. You receive all the group work and exercise files and a fully supporting, diagram rich user guide pdf. I always provide video solutions – so, you’re never on your own.

Still not sure? Here's what others have had to say about my "10 Easy Steps" series of courses:

DAX Power Pivot - 10 Easy Step for Advanced Users

"Once again, a masterpiece from Gilly to guide through the journey of advanced DAX. Step by Step diving deeper into DAX logic without loosing the line thanks to the outstanding effort of a well structured course which come along with a clear guideline, recaps and exercises to be able to adapt to own projects soon. Thanks so much Gilly!"

Doris.

DAX Power Pivot - 10 Easy Step for Advanced Users

"Another excellent course by Gilly. Covers a range of topics clearly and thoughtfully with challenging and interesting practice exercises."

Gary F.

DAX Power Pivot – 10 Easy Steps for Beginners

I thought that I was intermediate on this topic, so didn't bother buying the beginner course. However, getting stuck part way through the intermediate course, I decided to complete the beginner course. Boy am I glad I did - it has really consolidated things for me, and was well worth the money. If you haven't taken any of Gilly's courses before I can highly recommend them - she is super enthusiastic, keen, knowledgeable, and very responsive. If you only take one DAX course as a beginner, make it this one”.

Ian W.

DAX Power Pivot Time Intelligence – 10 Easy Steps

Great Course from Gilly. Her courses are a masterpiece and this subject was a tough one but she does a good job in explaining it Step By Step. Going by her own words you need practice to master it but at least I am happy the foundation is being laid with strong understanding to move forward. Thanks so much Gilly

Guna R.

DAX Power Pivot – 10 Easy Steps for Intermediates

It is obvious that Gilly has spent a lot of time with her study guide and organization of this class. This is one of the best illustrated courses I've found on Udemy. Explanations of the DAX formulas are very clear and flow logically throughout the course. The concepts discussed are easily at the Intermediate level as advertised. Truly an exceptional effort by Gilly Dow.”

Jimmy F.

Power Pivot is the perfect tool for those working in finance, statistics, project, business and market analysis or any data reporting role who have a need to create and produce Business Intelligence style Dashboard and Insight reports.

I have used Excel 2016 to demonstrate this fantastic tool but you can follow along in Excel 2010 & 2013 - I'll do my best to point out the differences. If you have any questions about the course, feel free to message me here or check out the FREE preview lectures to learn more.

Thanks for taking the time to check out my course. I can't wait to help you take the next step in developing your DAX skills.

Take the first steps to a new and rewarding future by clicking on the TAKE THIS COURSE button, located on the top right corner of the page.

Who this course is for:
  • DAX Power Pivot 10 Easy Steps for Beginners students
  • DAX Power Pivot Time Intelligence 10 Easy Steps students
  • DAX Power Pivot 10 Easy Steps for Intermediate students
  • Business Analysts and/or Intelligence Developers and Data Scientists who are confident with DAX fundamentals
Course content
Expand all 85 lectures 09:11:47
+ Introduction
1 lecture 06:33

Allow me to convey a very warm welcome and lay out the Course Outline. I'll also demonstrate how to download the all important Course Files and how to complete the Practice Exercises...

Preview 06:33
+ Warm Up Exercises
6 lectures 48:08

You will blow away the cobwebs and build a Data Model to display Year-On-Year % growth for a small company selling Masks... could you do this without a little help?

Preview 05:42

Is it RELATED() or RELATEDTABLE()?? Whichever it is, I need data from another table!!

Calculating with RELATED()
05:40

How to obtain seasonality insights using Time Intel and nested IF() statements...

SAMEPERIODLASTYEAR(), ISFILTERED() & HASONEVALUE()
14:35

How well are products selling compared to the Grand Total? Obtain useful Insights with ratio measures to keep on track of sales performance

Over-riding the current Filter Context with multiple ALL()'s
05:42

Check you're on track with the solutions to the Warm Up practice exercise part 1

Warm Up Practice Exercise Solutions 1
09:24

Check you're on track with the solutions to the Warm Up practice exercise part 2

Warm Up Practice Exercise Solutions 2
07:05
+ Step 1 - Variables
5 lectures 28:47

Take a couple of minutes as I take you through the objectives for Step 1. It really helps to get you into the right mind-set and prepares you for what's coming up AND it's a good one - you're going to LOVE IT!

Step 1 Objectives
01:20

In this video lecture you use Variables to reconstruct the Count Bands measure - this is AWESOME!

Recreating the Count Bands measure using Variables
09:39

In this second example using Variables - "simple filters" just won't cut it. Learn how FILTER() comes to the rescue when defining Variables that don't use comparative values and need a more "robust" solution

Getting to grips with the Rules of Variables and introducing FILTER()
08:03

Are you on track? How did you get on with the first Practice Exercise? Check your answers here with me as I take you through the solutions to using Variables in your measures...

Practice Exercise 1 Solutions
05:52

Let's take a minute or two to recap what you haven't just learned and ensure you embed, consolidate and under-pin the ins and outs of working with Variables...

Step 1 Recap
03:53
Quiz 1 Variables
5 questions
+ Step 2 Row & Filter Context
14 lectures 01:21:48

Take a couple of minutes to mentally prepare for what's ahead of you. Progress from DAX rookie to DAX ninja and get into the right mind-set for what's coming up!

Step 2 Objectives
02:01

Understanding the value of a measure is fundamental to your understanding of what's going on in the background. Watch and learn as I help you visualise what DAX is doing to display the end result...

What's the value of a DAX measure or a DAX formula?
05:40

Using Power Query to import data is a breeze and much easier to edit than Power Pivot especially when dealing with queries. In this video lecture I demonstrate how easy it is to import MS Excel tables and how to edit them...

Creating the Enrolments Data Model with Power Query
09:54

This is a build up to what is to come and it never hurts to cover the basics - AGAIN! Remember - you can never get enough of this - until you're repeating it in your sleep seems to be the time to STOP!

Row Context - Again!
04:55

Filter Context is the set of rows that are the coordinates obtained from the pivot table - but of course you knew that - didn't you?? Take a look at what's going on behind the scenes...

Filter Context - Again!
05:13

So, you know by now Calc Columns have Row Context - Measures don't! What about Calc Columns and Filter Context?? Step this way my friend and all will become clear (ok, clearer....)

Calc Columns & Filter Context
04:16

Rules just have to be broken and with the help of Iterator functions you can "manufacture" Row Context to limit the number of calculations you may need. Remember, you're reinforcing your knowledge and understanding whilst you build up to more complicated measures and calculations...

Breaking the Rules.... with Iterator Functions
04:08

You know how our most favourite DAX function in the entire world is CALCULATE() and how it enables you to manipulate Filter Context - right? Well, it can also turn Row Context into Filter Context - insane, but TRUE!

CALCULATE(), Filter Context & Calc Columns
06:52

Let's take a look at what happens when you enter a [Measure] into a Calc Column. Step this way my friend and be prepared to be AMAZED!

Measures & Row Context or "Magic"?
04:27

To put all you have learned to the test, you'll create a measure to display the Sales of Lowest Price Courses and address the CoursePrice discrepancy in the data table by editing with Power Query... simple!

Sales of Lowest Price Courses & Editing Power Queries
10:43

Before you see proof of concept when dealing with Measures in Measures, go behind-the-scenes with Power Pivot and follow what's going on in the background - it will help you visualise your own Data Models when things don't go according to plan!

Stepping through the No of Lowest Price Sales
03:05

Let's take a moment to pull all the Row Context, Filter Context, Measures and Calc Columns concepts together. I know it can be confusing what does what but take a look at this...

Measures in Measures...!
05:40
Measure in Measures Step Through....
08:22

Take a couple of minutes to reinforce your learning in Step 2. Having a firm understanding of Row & Filter Context is the foundation to excellent troubleshooting skills. You'll have to know this inside-out to be able to remedy common mistakes DAX rookies often make!

Step 2 Recap
06:32

Just how good is your Row & Filter Context knowledge? Find out as you try these fun Quiz questions...

Quiz 2 Row & Filter Context
10 questions
+ Step 3 Static Segmentation
6 lectures 31:23

Pause for a moment and remind yourself OR learn for the first time - just how useful Parameter/Disconnected tables are. You are building on your skills so, don't miss out!

Step 3 Objectives
01:03

Learn what Static Segmentation is and how to create and add the "Bucket" table to the Data Model...

Adding a "Buckets" table to display Course Level stats
04:11

Create the Calc Column CourseLevel with DAX VALUES() enabling pivot table filtering into segmented "buckets"

CourseLevel Calc Column with VALUES()
07:36

How did you get on? Did you manage to create the AdventureWorks Advanced Data Model with Power Query ok? Did your No of Highest Price Sales check-out ok when you double-checked it?

Practice Exercise 3 Solutions - Data Model & Measures
09:14

This is the 2nd part of Practice Exercise 3 where I demo adding the parameter table to the Data Model and create the calc column for the Static Segmentation exercise.. Did you nail it?

Practice Exercise 3 Solutions 2 - Static Segmentation & Calc Column
05:39

Time to Recap all you have remembered or learned in Step 3 - it's only a couple of minutes of your time and you could pick up some valuable tips for the Quiz! Do you SWITCH() or Parameter when you need to Segment??

Step 3 Recap
03:40

Take a tea-break Quiz and test your knowledge of Static Segmentation... it's just a bit of fun that serves as a great learning tool

Quiz 3 Static Segmentation
5 questions
+ Step 4 Multiple Data Tables
9 lectures 57:18

This is going to be GREAT!!
Multiple Data tables - can it really be!!?? Step this way and prepare to be AMAZED as I take you through the Objectives of this incredible concept!!

Step 4 Objectives
01:46

Chances are you will have a Calendar table from your primary data source. But, what if you haven't? Power Pivot provides an easy solution with just a couple of clicks - AWESOME!!

Creating a Calendar Table from within Power Pivot
07:59

You will learn how to add a SECOND Data table to the Data Model and the rules that must be followed to ensure sharing data between them is seamless... This is AWESOME stuff and where your DAX "Ninja" status really starts to develop...

Adding the Service Call Log SECOND Data Table
07:25

Setting up a couple of pivots to demonstrate how DAX handles data between Multiple Data tables is truly inspirational. Step this way to be blown-away!!!

Sharing Data Between Data Tables
07:40

Next, you add to the Data Model and provide a Customers Lookup table for BOTH Data tables to explore AND filter

Adding Customers Lookup to BOTH Data tables
06:48

You have added the second Service Calls Data table and displayed preliminary Insights. In this video lecture your aim is to delve deeper to reveal what the true cost of service calls really is...

The True Cost of Service Calls?
07:01

Test yourself before viewing the Solutions to Practice Exercise 4. How did you get on? Ensure you are on track to proceed and pat yourself on the back for a job well done!

Practice Exercise 4 Solutions
10:04

Did you spot the issue? Have you understood why the error message displays? Check your reasoning with me as I provide the Solutions to the second part of Practice Exercise 4.

Practice Exercise 4 Solutions 2
03:37

Let's take a quick tea break and quickly recap what you have just learned. It's quite a milestone you have achieved in your DAX journey and getting the basics right will help you tackle more complex scenarios that you will face later in the course. Make sure you're on track and raring to go!

Step 4 Recap
04:58
Quiz 4 Working with Multiple Data Tables
5 questions
+ Step 5 RANKX() & TOPN()
9 lectures 01:03:10

Prepare to be amazed with a glimpse of what's to come in Step 5. It really helps to get into the right mind-set and I'm introducing 2 brand new DAX functions in the series - how exciting is THAT!

Step 5 Objectives
01:08

In this next video lecture I demonstrate how easy DAX enables you to rank student sales. Using the DAX RANKX() function you will learn how to manipulate the default settings to display exactly what you need to correctly display "ties" in sales amounts and avoid "gapping" in RANKX() results

Student Sales
09:21

Create the Channels calc column to enable slicing and display where sales are originating and Rank to target your strongest and weakest customers

RANKX() by Channel
07:04

This DAX function is simple in concept but a little tricky in logic. Once the matter of "current context" is appreciated - the penny will drop and it's another super little tool to have up your sleeve!

TOPN()
09:35

In this video lecture, you edit the existing TOPN() formula to help ascertain the performance of a new course...

Using TOPN() to track new Course release - "May the Fourth be with you"
04:36

How "top heavy" are we in terms of Beginner courses? Has the launch of new courses had an effect on our flagship courses? Use TOPN() is display revenue performance...

Using TOPN() to track Revenue...
08:41
Using TOPN() to display Best-Sellers
04:53
Practice Exercise 5 Solutions
09:52

Ok, that was a bit tricky! You're "logical reasoning" was tested BUT the results in our Enrolments table were very insightful. You may not need these "powers" on a day-to-day basis - however, employing logic is never too far away when and if you want to be a DAX ninja! Take a few minutes to consolidate what you've just achieved. It will embed your understanding, answer any queries that may still be lurking AND you might just pick up a couple of hints for the quiz!

Step 5 Recap
08:00
Quiz 5 RANKX() & TOPN()
5 questions
+ Step 6 Different Granularity
7 lectures 50:40

What do you do when you receive a new Data table source which doesn't match the time period of your primary Data table? You need to insert an "Intermediary" table to bridge the gap. In this Step you learn how to do just that and how to display data correctly in pivot tables to accommodate choices made by the end-user. Exciting stuff and advanced techniques to set you on your way!

Step 6 Objectves
01:58

You have received data for Advertising Costs for the MasksToGo data model. In the video lecture you learn how to share data with the Sales table to display percentage costs from the new Data source  and Total Net Revenue from the existing Sales Data table

Adding the Advertising Costs DATA table
05:21

Next, you learn how to identify which fields on the pivot table are filtered in order to create the required measure and how using the little known or used NOT() function can help you out of a tricky situation! Genius status awaits you!

ISFILTERED & NOT()
07:34

You are feeling empowered and ready to take on the world! Until, that is, the new Data source arrives in a different format that you're used to. However, you are a DAX "ninja " - and this won't stop you. Learn how to overcome the next hurdle in your DAX journey. Remember, you're building your skills - therefore, perhaps not all your questions may be answered - but, they will!

Costs Variation
09:08

As always, the client brief has altered. They won't MORE! In this video lecture, you learn what's required to provide options by editing the measure - that has served you well so far - to include multiple Date fields. You're becoming a "Legend" in your own lifetime!

OR() & ISFILTERED_()
08:00

Did you NAIL IT?!!  I'm betting you did. Check you're on FIRE as I reveal the Solutions to Practice Exercise 6...

Practice Exercise 6 Solutions
13:20

You've come a long way. Let's recap what you have just learned and I'll try to fill in any gaps, queries or remaining questions you have lurking. Time for a cup of tea and a biccie!

Step 6 Recap
05:19
Quiz 6 Different Granularities
5 questions
+ Step 7 Budget vs Actuals
6 lectures 38:07

To ensure you are in the right mind-set for what's to come, watch as I take you through the objectives for Step 7. I prepare you for an "unexpected" issue in the end of Step Practice Exercise and what you'll need to overcome the problem...

Step 7 Objectives
01:45

Data tables and Lookup tables require a common field in order to share data. Learn how to identify that field and create it in the required table/s

Creating the "Connection" field using DATE()
07:04

Now you have the relationships in place to enable data sharing between multiple Data tables, create the Budget vs Variance measure

Budget vs Actuals Variance
09:28

Did you manage to deliver the "Brief " ? Watch as I complete the challenge and double-check you're on track to carry on!

Practice Exercise 7 Solutions
10:11

In this video lecture I demonstrate what to do when the dreaded "..duplicate values " displays. It's all about the "unique-ness" of values in the Lookup tables and how to create them!

Creating "Intermediary-Lookup" Tables
06:36

So, you now have another tool in your DAX armoury! What else have you learned in Step 7? Take a couple of minute to recap the objectives and fill in any gaps...

Step 7 Recap
03:03

It's just a bit of fun but it does reinforce your learning and the key concepts. How well can you spot the solution to a "relationship" problem?? Now there's a question!

Step 7 Quiz
3 questions
+ Step 8 Budget Patterns YEARLY
6 lectures 40:04

You are about to learn about "Budget Patterns". I'll outline the objectives to ease you in to this massively useful concept with a simplified version of the full Pattern which is coming up in later Steps

Step 8 Objectives
02:49

To understand what is required to display the Budget results you will take a look at how regular Excel is used to create the results. Ok, this is not an Excel course - however, it will prepare you for the calculations required to complete the same task using DAX

Setting the Scene
09:54

In this video lecture you create the "component" measures that are used to display the Budget figure. This is replicating the calculations used to create the Excel version of results. Why? To prepare you for the "Pattern" that is used in the "All-In-One" approach...

Creating the Budget MANUAL
06:12

Creating 3 measures to obtain a result is not the most efficient solution. Now that you are familiar with the components that provide the solution use this knowledge to create an "All-In-One" approach to display the Budget Allocation measure down to the Day level. You are building your knowledge of what can be achieved given the information supplied.

Creating the Budget Allocation All-In-One Approach
08:23

How did you get on? Did you manage to display the Budget Allocation of Courses down to the Day Level? Watch as I provide the solutions and ensure you're on track to take it to the next level!

Practice Exercise 8 Solutions
08:24

The "Budget Pattern" is such a useful calculation in its own right. However, it also highlights the potential of other calculations that can be "bundled" together to reduce the number of unnecessary interim measures. Let's recap the key concepts and allow me an opportunity to answer any remaining queries you may still have...

Step 8 Recap
04:22

Test your knowledge of the Budget Pattern - did you understand the calculations required to answer a few simple questions?

Step 8 Quiz
5 questions