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:
— Bill Jelen (@MrExcel) February 1, 2014
— PowerPivotPro (@powerpivotpro) February 1, 2014
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:
- Steal someone else’s technique for Many to Many (thanks Gerhard!)
- Learn new functions I’ve never used before – CROSSJOIN and SAMPLE – to layer on top of said stolen technique
- Create intentionally inactive relationships (!) – I’ve done this before, but never had them be the ONLY relationships between tables.
- 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.
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.