DAX Power Pivot 10 Easy Steps for Advanced Users

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?

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

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

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

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

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

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!

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

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

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...

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...

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!

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...

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...

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!

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...

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....)

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...

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!

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!

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!

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!

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...

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!

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!

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

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

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?

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?

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??

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!!

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!!

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...

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!!!

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

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...

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!

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.

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!

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!

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

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

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!

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

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...

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!

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!

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

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!

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!

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!

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

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!

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...

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

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

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

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!

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...

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

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

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 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.

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!

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...