skip to Main Content


Un.  Freaking.  Believable.
(See bottom of post for video of this in action!)

Back to Our “Regular” Programming Today

First of all, THANKS for being patient with the promos for PowerPivotPro University – we do have to pay the bills here of course, AND I have poured six months of my best work into it, so thanks for giving it your consideration.

But yeah, it’s time to get back to some serious magic tricks with data.

A Funny Thing Happened On Reddit and Twitter…

I don’t play Pokemon – neither the video nor card game variety.  But I recognize my kind of sickness (cough cough fantasy football) when I see it.

And people who know me, ALSO know my sickness.  So they point things out to me:

And that led me to this Reddit thread:  Pokemon & Power Pivot…



…which included, um, THIS:


Power View Report for Analyzing Top “3-Mon” Teams:
If I Were a Shark, this Would be Chum.

The Challenge:  Build a 3-Pokemon Team With Good “Raw” Score But Minimized Shared Weaknesses (No Kryptonite Allergies)


Example:  This Team of 3 Pokemons Might Have a Very Good “Raw Score” –
All 3 Individuals Are Tough – But as a Team, They Have an Achilles Heel:
A Shared Weakness Against Shadow Opponents

Pokemon is like a game of “Rock / Paper / Scissors” (R/P/S), but instead of 3 types there are MANY – Water, Air, Earth, Fire, Shadow, Metal, Ice, etc.  Water beats Fire, Air Beats Earth, etc.

But it’s even more complex than that – unlike in the childhood game of R/P/S, each Type “beats” more than one other type.  So, Water beats Fire, but also, say, beats Ice.

And superiority is not “binary,” it’s numerical – So, Water might have a 2x advantage over Fire but only a 1.5x advantage over Ice.

Further complicating things is that each Pokemon can be “of” more than one Type!  A Pokemon that looks like a steel shark, for instance, might be both Water and Metal.  So that Pokemon is weak against Water’s weaknesses, AND against Metal’s weaknesses.

Lastly, each Pokemon ALSO has a “raw” score – if you ignore the whole R/P/S weakness thing above for a moment, each Pokemon also has an inherent “raw strength” score.  A Pokemon with a raw score of 90 is probably better than a Pokemon with a raw score of 50.  At least, until the R/P/S dynamic kicks in.

The goal is to assemble a 3-Pokemon team that has long-term survivability.  This requires a good combined “raw score,” for sure.  But you want to carefully limit your team’s weakness against specific Types – it’s no good to be Superman for 49 battles and run into Kryptonite in the 50th.

And there are HUNDREDS of Pokemon to choose from.  Computationally, this might sound like a job for IBM Watson.  But it turns out, Power Pivot can do it.

I Take Up the Noble Cause

The author of the Reddit thread (and the Power View workbook pictured above), Tycho Grouwstra, is just your average guy.  You know, born in Holland, moved to China as a freelance professional of international intrigue, picks up Power Pivot on a whim and tackles one of the hardest problems I have ever seen – THAT kind of person.  Yawn.  I meet these people all the time don’t you?

Tycho’s workbook is a triumph of the human spirit.  Tons of tables and complex cross-table lookup calc columns.  I wanted to see if I could find a more elegant approach.

So we jumped on Skype.  Everything above about Pokemon, I learned from Tycho on that call.

File Under “Brain Candy, Ultra Spicy”

OK, let’s just be clear up front:  today’s technique might be the craziest thing I’ve ever done in Power Pivot, except for maybe the Calendar Chart.

Longtime readers may remember the DAX Spicy Scale.  Well, this one is like spicy level NINE.  Do not, for a moment, feel like you have to understand this one.  I’ve been dreaming in DAX for 4+ years now, and in order to do this, I had to:

  1. Steal someone else’s technique for Many to Many (thanks Gerhard!)
  2. Learn new functions I’ve never used before – CROSSJOIN and SAMPLE – to layer on top of said stolen technique
  3. Create intentionally inactive relationships (!) – I’ve done this before, but never had them be the ONLY relationships between tables.
  4. Beg for help from my old buddies at Microsoft (thanks, Jeffrey!) when I hit a snag

OK, so, one more time, with feeling:  do NOT feel like you have to understand this!  Just marvel at the power of our favorite tool.  It can damn near do anything.

(Aww Yeah)

Let me know of other “set optimization” problems!

I don’t think this sort of analysis is limited to games.  In the real world, I’m pretty sure we occasionally run up against something similar.

So, if you have a real-world problem along these same lines, let me know.  Maybe we can adapt this technique to address it.

Download the Workbook!

Download the Workbook Here

Rob Collie

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 15 Comments
  1. I haven’t had a chance to go through the entire video, but would this technique be able to be used for fault fantasy football it basketball games where you have to create a lineup, but you’re limited by a set salary? Like Fanduel for example. I’ve worked on building something for this purpose and have used Power Pivot to create my projections each day, but the final piece of determining the optimal lineup was a linear Solver model (which I’m not entirely sure provides the global best solution every time)

    1. Interesting! On one hand, the problem you describe is simpler than this one. It’s not about weird/complex many-to-many interactions, but optimizing one variable while minimizing another – both of which are linear calculations (sum up value, sum up cost).

      On the other hand, your problem may be much worse, given the number of “slots” you have to fill and the number of options in each slot. I’m worried about the CROSSJOIN producing billions of combos. In my case, 13 * 13 * 13 = 2197 combos. But 32 * 32 * 64 * … = what? 🙂

      Without thinking on it too deeply, I’d say that linear Solver might come out on top still – in terms of cost/benefit. But I can’t be sure. Maybe you should give it a try and report back 🙂

  2. Just occurred to me, thanks to Bryan’s comment, that this technique might be useful in the financial/securities industry. AKA Wall Street.

    Energy stocks are susceptible to certain flavors of geopolitical events – a different set of events than what negatively impact, say, Biotech stocks. Just like Fire Pokemons are vulnerable to different attackers than Earth Pokemons).

    The problems are structurally VERY similar. I’m not at all sure that this approach would be useful in actual practice, but someone from that industry might know better.

    (Of course, if this technique IS helpful on Wall Street, it’s likely we will never hear that it is. It will be kept secret 😉 )

    1. Hi Dan. Over here I have a pure Excel version that attempted simulating battle matchups, though the actual complexity is gonna be hard to capture with anything but full-fledged chess-style minimax game tree parsing in a structured programming language (which, by the way, has been done as well — I started converting that spreadsheet as well, but I’m still trying to figure out the rest of the DAX for the PowerPivot version…

  3. Rob – you have a new follower – My 10 Year old. He and friends are crazy about Pokémon and now its time to get him started with PowerPivot

    1. Hey, we’re changing the world, one convert at a time.

      I suppose a Pokemon model is kinda like Ronald McDonald for Power Pivot. Although, this is a terrible first workbook for anyone to be exposed to. It’s like McDonald’s trying to use Freddie Krueger as a kid-friendly “face” as opposed to Ronald.

      Now that I mention it, I don’t think I’ve seen Ronald in a long time. I wonder if they’ve retired him to avoid future lawsuits about deliberately targeting children.

  4. Hey Rob, great Technique. I do have a similar problem actually, where I am not only interested in the top value but more the top ten.

    I have a large number of orders, each with several product items included. Each item has a category id. I now want to find out which category combination is included in the most orders.

    I.e. something Like a TopN table over all category combinations.

    In addition I want the user to be able to select one category and the tool will show the top matching other combinations.

    I have created a report that does this, but it’s not an efficient or elegant solution, as I am simply creating a massive excel matrix, that calculates each combination.

    Do you have some ideas?

    1. No promises Konrad, but if you send me a workbook containing sample data, I’m certainly intrigued enough to give it a shot 🙂

  5. This is actually a pretty cool decision modeling problem. If you mathematically represent how one pokemons strengths and weaknesses compare to every other pokemons, then you can tell Solver to find the optimal for a team of 3. I believe its an integer program.

    However, Solver is not nearly as cool as power pivot, nor does it output pretty pictures and lists. Cool solution to a tough problem.

  6. I’m sorry but I have to point this out. Pidgeot’s and Rattata’s photos are swapped. I will go back to watching the video now.

    1. Ok, now that I have finished the video I can do something besides critique on the improperly named images (which there are a few more but this also isn’t the standard ‘Pokemon’ crowd).

      Awesome stuff, once again you taught me a new function, USERELATIONSHIPS(), AT THE EXACT MOMENT I needed it.

      Some of these concepts may be able to be used to determine what type of product mix one would want to run. Be able to compare against, inventory size needed, profit margins, percentage of returns, cost of returns, shipping costs, etc to determine what the ‘ideal’ stock or run line would be. Now that I think of it that report would be a lot like what your ‘Football Draft Picker’ spreadsheet was doing but without the need to calculate opposing picks.

      Great stuff, more fuel for my creative fires.

  7. I can see this type of analysis being applied wherever you have multiple inputs with multiple properties. Such as Risk Management or Just In Time vs. Near To Time material inputs in manufacturing (how near is near?), Cost Benefit analysis of money/time compared to total benefit applies to so many business decisions that this could remove a lot of emotional attachment and guesswork from the process.

    You may have just discovered the killer model (in a basic template form) for PowerPivot in the Big Business world. I am sure that you, Rob, can extend these to a much larger range of real world applications.

Leave a Reply

Your email address will not be published. Required fields are marked *