In this video lecture we take a look at what the problem is with editing queries in Power Pivot's Data Model window - you need to know why the change to Power Query is necessary and the issue it will overcome

In this video lecture you will start importing queries via Power Query. By the end of this exercise you'll to confident with the various steps and elements that make up Get & Transform, the Navigator and the Query Editor

You have completed the Transformations including changing date formats to your local Regional Settings. Next, you will create a Connection Only to the data source and create One-to-Many relationships in the Data Model window

You're confidence is building - you want to add more queries. Learn how easy reconnecting to an existing data source is and how importing new queries is as simple as 1,2,3...

The reason why! This is Power Query doing what Power Query does best - EDITING QUERIES!

The first Practice Exercise gives you an opportunity to hone your new skills. Consolidate - Embed, feel GREAT about your new skills!

1. Adding a New Query
2. Editing an Existing Query (bliss)
3. Creating a New One-to-Many Relationship!

In this video lecture, I provide the solutions to keep you on-track and reassure you you're doing a GREAT JOB!

Take 5 minutes to reinforce the key concepts covered in Step 1 - You might just pick-up a couple of hints for the Quiz!!

You have heard the expression Initial Filter Context but do you know what it really is? In this section you create a couple of new measures and explore how the pivot delivers the values displayed. By the end of this section you'll have a clear understanding of the role of Aggregator functions and Iterator functions - a useful recap to set yourself up nicely for the remaining steps in the course

Nail the difference between Aggregator functions and Iterator functions - which of these do you use and when? By the end of this exercise you'll be in no doubt!

You now have a solid understanding of Row Context. By the end of this lecture you will create new measures with SUMX() - your new BFF! (best friend forever)

Check your solutions to practice exercise 2 - ensure you're on track and ready for Step 3!

Take 5 minutes to reinforce the key concepts covered in Step 2 - You might just pick-up a couple of hints for the Quiz!!

This section is all about Visualisation and what is going on behind-the-scenes. You'll "see"  Expanded tables helping to produce the Filter Context and know when to use Aggregators and when to use Iterators. Next, I'll introduce CALCULATE() and you'll learn how to override the Initial Filter Context with Explicit conditions - WOW! Lots to get your teeth into...

Follow the "path" of the "Initial Filter Context" and learn how results are displayed in the pivot table. This knowledge will help you visualise what's going on making troubleshooting a breeze. All the best DAX experts know this stuff - that's why they're EXPERTS!

Feeling empowered? Take this knowledge a step further and let's throw in an Iterator for good measure - get it! You're well on your way to EXPERT status!

So, you've got Aggregators and Iterators in the bag - now let's throw in what CALCULATE() can do. In this lecture you setup the pivot and measure in preparation to follow what CALCULATE() brings to the table - get it!

You have created the CALCULATE() measure. Now follow the journey it takes to display the "Initial Filter Context". Will the result be expected or a surprise? Your new level of understanding wont' fail you!

Did you visualise what's going on behind-the-scenes as you completed Practice Ex3A? Check out your answers are correct and the technique has paid off as you watch me complete the first part of the solutions

Watch as I provide the solutions to Practice Ex3B and ensure all that good work has paid dividends! Do you know your AVERAGE()'s from your AVERAGEX()'s??

Take a few minutes to recap all that you have learned. Preempt all I say with your knowledge and powers of Visualisation!

Being clear on your objectives improves performance significantly - take a couple of minutes to prepare for your next "challenge"

In this lecture you learn how Row Context, Filter Context and Context Transition work with Calculated Columns. See the difference Aggregators and CALCULATE() have and what changes occur when they are used

You know how Context Transition works with Calculated Columns. In this video lecture you learn how Context Transition and Measures work together. Nail DAX behaviour to set you up for troubleshooting skills with a solid understanding of how to build your calculations!

Round up this section with a quick recap on the key concepts of this Step - you might just learn a thing or two for the quiz up next!

Check out what's coming up in the next step of your Intermediate DAX journey. Being clear on your objectives mentally prepares you for the way forward.

In this lecture you set the scene for using the VALUES() function. You will see that sometimes your totals and/or grand totals don't add up to the sum of their children?

The VALUES() function can be used in place of the Table parameter that a lot of DAX functions require. Learn how, when used with SUMX(), this solves the problem of "Parent" and "Children" total and subtotal problems - it's a fantastic solution and another great tool to have up your sleeve!

Learn some new techniques with the VALUES() function. Are you on track - are your solutions the same as mine? Check you're answers with me as I step through Practice Ex5

Round off this section with a reminder of how far you have come and why VALUES() is going to be an important addition to your DAX tool-kit

The ALL() family includes a couple of "gems" that you may not get to use too often BUT when the need arises - you'll be glad you know about them. Take a couple of minutes to see what's coming up and how useful these DAX functions can be!

Blow away the cobwebs and create a couple of base measures using ALL() - great when you want to create comparative calculations! ALL() can be thought of as the "Remove Filters" functions and is hugely useful...

The difference may be subtle but the results displayed when passing the Table and passing a Column in the ALL() function does make a difference. Follow along with me as you create 2 new measures to highlight this little gem!

ALLEXCEPT() can be used to exclude a column from a table - a great tool to have up your sleeve when you most need it and a better alternative to listing all the columns you do need!

ALLSELECTED() works in harmony with Slicers - when you want totals to reflect the percentage of the selected values - ALLSELECTED() is the answer. By the end of this section - you'll look for reasons to use this fantastic addition to your DAX tool-kit. It's not only hugely impressive, it's a whole lot of fun too!

Make sure you're up-to-speed and got a firm handle on the ALL() family of functions with the Solutions to Practice Exercise 6 questions 1 & 2

Check your answers as I complete the Solutions to Practice Exercise 6 questions 3 & 4. Have you got your ALL()'s in the bag?

Take 3 minutes to Recap the Objectives for Step 6. Be super-clear on all the concepts covered and feel confident, empowered and raring-to-go as you move forward with your DAX learning!

Your objectives for Step 7 coming up next to ensure you're clear on the concepts to take you forward on your DAX journey

Say goodbye to those annoying and useless subtotal and grand total values that make no sense with your new best friend - HASONEVALUE(). Not sure what this means to your reporting? Watch, learn and marvel how you've managed until now!

You've got a handle on how useful this function is. Try your hand at another example and get to grips with this really useful addition to your tool-set

Early in Step 5 you were introduced to the VALUES() function. Use this in conjunction with HASONEVALUE() to introduce text return measures - genius!

I have included this to help with Practice Ex7 - using VALUES() without an IF() condition

How did you get on with the latest Practice Exercise? Check you're on track as you watch me complete the tasks...

Let's go over how far you have come - you should be feeling invincible by now!

Take a couple of minutes out for a "heads-up" on what you're about to learn - you don't want to miss this - Disconnected Tables ROCK!

Wouldn't it be GREAT if you could hand over control to the end-user and enable them to choose which projected value they would like to see? Well now you can! Learn how to "harvest" that end-user selection and hand over the flexibility directly to them! This is AWESOME!

The "Disconnected Table" is added to the data model, the "Harvester" measure is created... let's get the finishing touches to the slicer settings and formatting and finish off with the measure that harvests the end-user selection

You have all the tools in place - you know what the objectives are. Let's go back to the Sales Report and finish what we started by creating the "Harvester" measure and the "Conversion" measure

How did you do in Practice Exercise 8? Watch as I complete the exercises and ensure you're on track to move on!

You have come a long way - you should be proud! How much did you absorb in Step 8 - how much do you know about Disconnected Tables and Harvester measures?

Want to know how awesome you are going to be after you have completed this next section? Then step this way my friend and prepare to be AMAZED!

Create the first 6 steps required for the Customer Yearly Income report and provide a "Threshold" parameter for the end-user to select what they wish to view - GENIUS! Can you remember the Mnemonic learning-aid to help you through this latest challenge?

Create the "Conversion" measures required to complete the Customer Yearly Income report with the FILTER() function

Have you mastered Thresholds? Watch as I complete Exercise 9A and check your solutions match mine!

Now that you have Thresholds sorted, you're half way to Banding. You know the Mnemonic, only this time you have 2 "Harvester" measures to create - easy!

You're nearly there - just the "Conversion" measures to complete the report

Try out another example of Banding with the emphasis on Calculated Columns to achieve your objective. To "Harvest" or not to "Harvest" THAT is the question!

You're almost there - you've know the Ages of your customers and created the Age Bands - now for the pivot table "Base" measures. Insight heaven is just a few clicks away!

How did you do? Have you mastered Banding - check you're on track with me as I provide the solutions to Practice Exercise 9B

If you chose to test your skills that little bit further with the FastTrack Extra challenge, you can check your solutions here with me

Let's take a look back at what we've covered in this penultimate Step in our DAX journey - you've come a long way!