skip to Main Content

power pivot to power bi

A Power BI Technique Mined from the Power Pivot Archives

Below, you will find one of our all-time favorite Power BI techniques. In the ten (yes ten) years we’ve been operating this site, we’ve written over 1,000 articles on Power BI.  Hard to imagine - even for us - but true.

Years ago, we first wrote up this technique in the context of Power Pivot – Power BI’s cousin and predecessor, which we like to call “Power BI in Excel.” 

Since the two products share the same brains (DAX and M), this technique is more relevant today than ever. Everything below is 100% legit for Power BI – the same data model “shape,” the same DAX, etc. – but now you can use it to power up your Power BI visuals, and not just PivotTables.  Enjoy! Smile

Excerpts from PowerPivotPro School

Since it’s been the primary focus of my time lately, for today’s post I thought I’d share two excerpts from the upcoming PowerPivotPro School.

Intro to CALCULATE()

The first one is the complete and uncut introduction to the CALCULATE function:

That’s only the intro of course.  There is a lot more on CALCULATE obviously.  Even the “basics” consume quite a bit more time in the course, but I thought you’d like to see this first part.  I hope it helps some of you even if you don’t choose to join the course.

How Measures Are Calculated – Three Minute “Montage”

Chronologically, this clip occurs before CALCULATE in the course.  This is from the module called “Warmup and Fundamentals.”  It’s 3 minutes of “highlights” spliced together from a longer, 20 minute section:

Whereas the first video above is at “normal speed,” this one moves faster simply because I’ve edited out a lot of the more complete description and background.  The actual course moves more deliberately.

Sign Ups Open Until August 15th

If you’re interested in joining us for the course, signups remain open until August 15th.  Hope to see you there:


I’m getting a lot of questions about how the course is structured and delivered.  I’m compiling a FAQ and should post that tomorrow.

Rob Collie

One of the original engineering leaders behind Power BI and Power Pivot during his 14-year career at Microsoft, Rob Collie founded a consulting company in 2013 that is 100% devoted to “the new way forward” made possible by Power BI and its related technologies. Since 2013, PowerPivotPro has rapidly grown to become the leading firm in the industry, pioneering an agile, results-first methodology never before seen in the Business Intelligence space. A sought-after public speaker and author of the #1-selling Power BI book, Rob and his team would like to help you revolutionize your business and your career.

This Post Has 11 Comments
  1. The Intro to Calculate() is well presented and informative. Not sure about the voice, though. I started to feel sleepy at about the 15 minute mark. One of us would need a double-shot of espresso for me to be able to stay lively for a 30-minute session 😉

    Uh-oh, you know what popped into my head? Making the Adv. PP videos the feature presentation at EUG meetings! Only that would be naughty. Unless there was a special group license for just the videos… ehem.

    1. Hey, the fundamentals aren’t the sexiest topic. I thought I did pretty well making them interesting, all told 🙂

      Anyway, I think the fun factor picks up steadily, once the fundamentals are established.

  2. Thank goodness, you were able to incorporate your drawings into the video. What is a power pivot training without massive amounts of scribbling on a white board??

  3. Good explanation of that very issue (which I had come across before). Of course what I really want to learn now is how to make calculate more dynamic, how to use the filters aspect without having to hard wire something into it e.g. bikes as you have used.

    1. Yes, and naturally, that is covered. Many different ways actually, since the CALCULATE function is the basis for basically all such magic in all its flavors 🙂

  4. I signed up, but I was wondering are all videos for the class going to be released on August 19? Or a couple of videos each week?

    1. It’s scheduled for about four hours of training each week. We will sustain that for at least three weeks and maybe more depending on length of the bonus content which is still tbd.

Leave a Comment or Question