DAX Power Pivot – 10 Easy Steps for Intermediates

Build Power Pivot Data Models with Excel 2016 Power Query – take your DAX Business Intelligence to the next level

This course builds upon the skills learned in the first course in this series DAX Power Pivot – 10 Easy Steps for Beginners.

What you’ll learn

  • You will learn how to create a Data Model using Get & Transform in Excel.
  • Learn how to use “Disconnected” tables (Parameter tables) to maximise Insights.
  • Expand your understanding of the basic DAX Aggregator and Iterator Functions.
  • Understand the Initial Filter Context, Filter Context and Context Transition.
  • Gain confidence building measures with multiple functions – advancing your DAX tool-kit.
  • Learn how to catalogue your data model functions in Excel.

Course Content

  • Introduction –> 2 lectures • 11min.
  • Step 1 – Creating the Data Model with Power Query –> 7 lectures • 47min.
  • Step 2 – Basic DAX Iterators –> 5 lectures • 32min.
  • Step 3 – Multiple Tables & CALCULATE() –> 8 lectures • 44min.
  • Step 4 – Context Transitions & Measures –> 4 lectures • 22min.
  • Step 5 – VALUES() & SUMX() –> 5 lectures • 26min.
  • Step 6 – ALL(), ALLEXCEPT() & ALLSELECTED() –> 8 lectures • 46min.
  • Step 7 – IF(), VALUES() & HASONEVALUE() –> 7 lectures • 36min.
  • Step 8 – Disconnected Tables (Parameter Tables) –> 6 lectures • 41min.
  • Step 9 – Thresholds & Banding –> 11 lectures • 1hr 19min.
  • Step 10 – Key Performance Indicators –> 7 lectures • 46min.
  • BONUS –> 4 lectures • 21min.

DAX Power Pivot - 10 Easy Steps for Intermediates


  • This course is the next course in the DAX Power Pivot – 10 Easy Steps for Beginners and/or DAX Power Pivot Time Intelligence – 10 Easy Steps series and uses Excel 2016.
  • Your Excel environment will have access to Power Pivot.
  • 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).

This course builds upon the skills learned in the first
course in this series DAX Power Pivot –
10 Easy Steps for Beginners.

You have already gotten your hands dirty with Power Pivot and seen how awesome it is.
Now you are ready to take your skills to the next level by delving deeper and acquiring
a more thorough understanding of how “Disconnected
or “Parameter” tables can be utilised
to gain additional insights in your data models.

The first part of the course addresses Microsoft’s change to
the DAX engine that now makes
editing queries a challenge if you
aren’t an expert in SQL. Therefore, you learn to use Get & Transform to import data and in doing so, reduce the size
of your data model file size considerably.

You will draw upon your beginner DAX skills to delve deeper into the real meaning behind “Initial Filter Context”, “Filter Context” and “Context Transition” with explanations,
diagrams, examples and practice exercises.

To leverage the capabilities of “Disconnected” tables, I introduce you to the concept of thresholds, cut-offs and banding.
This really does open the door to a wealth of possibilities and you’ll learn
some useful techniques to gain some powerful new insights along the way.

Throughout the course, you are building your skills and understanding
– taking you to the next level at each Step

I’ll have a couple of surprises
for you along the way – not only to boost your brownie-points at work but for
your own piece of mind around administration of your data models!

Here’s what others said about the course:

Gilly. Did it again with the Addition of
Dax PowerPivot-10 easy steps for intermediate course. This intermediate Course
adds more formulas and Dax functions and even disconnected slicers and cube
formulas to add your learning base and continues to build off the previous two
courses. Gilly is a brilliant teacher who clearly has an in-depth understanding
of how power pivot works. She can break it down and simplify Dax so you know
how functions works and understand how to use it in your day-to-day excel work.
If you follow along and do the exercises when you’re done with a lesson you
fully understand how to complete and use the Dax formulas in your data work. If
you want to understand and learn Dax, Gilly courses are what you want to start
with. I recommend Gilly courses, you will be pleased with your results
afterwards. Well done Gilly. Thanks

– SD

OUTSTANDING COURSE Love the exercises, really enforces the instruction Learning so much, will go over the course again reinforce learning Everything is explained in easy to understand terms

– JM

“A lovely paced course with the different
elements nicely segmented and of a good length. Certainly liked the extra
surprise of Cubes. Will aid in displaying data that does not fit well with the
confines of a pivot table. Cheers Gilly

– GB

“Do Love the way you teach. Hope to learn
more from you for advanced DAX course with real world case studies. Bravo!

– HA

“Trainer explains concepts really well
without using too much tech talk. The many examples support the good
understanding of the subjects. The speed is ok for advanced users but too fast
if you did not follow the basic course. Course is complete and every important
subject is explained.”

– R

“Gilly does a great job of explaining
complex concepts and makes her points using thought-provoking analogies.
Excellent instructor!

– SA

“The course continued to build on the 10
Easy steps for Beginners. Detailed Instructions and real-world examples. Deep
dive into advanced Power Pivot Formulas.

– CJ

What you will learn
in this course:

  • Get & Transform – building the Data Model
  • DAX Iterators & Aggregator Functions
  • Initial Filter Context
  • Filter Context
  • Context Transition
  • VALUES()

Disconnected/Parameter Tables

  • Creating/Adding Disconnected tables
  • Harvester” Measures
  • Conversion Measures
  • Thresholds/Cut-offs
  • Banding
  • KPI’s

I provide examples
that we work through together. Then it’s over to you with practice exercises for you
to help embed and consolidate your skills. I always provide video solutions – so,
you’re never on your own.

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
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
button, located on the top right corner of the page.