Rip van Winkle wakes up and looks around
I have a confession to make: when it comes to technology, I’m a much better writer than reader. I don’t pay nearly as much attention to what everyone else is saying in their books and on their blogs. I’m too hunkered down in my own little world, figuring things out my way, to notice all the good stuff that may be going by.
Sometimes you just have to accept who you are though. The way I work… works for me. I grind away on things, like erosion, until a simple picture emerges. (And then I screenshot that sucker with WinSnap and throw it on the blog next to a movie quote, heh heh.)
Well something got into me recently. I started checking in on SQLRockstar’s blog a bit more often, because MAN – that guy puts together a simultaneously technical and human series of posts, with ridiculously high production values. I don’t know SQL and I don’t intend to learn, but I read anyway. I like to think what’s happening on his site is similar to what happens here: tech is being yoked and bent to serve humanity, not vice versa.
Oh and you definitely should check out his Disclosures page. I mean, what if he were part owner of the gentlemen’s club whose explosion he covered? I’d want to know that, and it’s very responsible of him to come clean up front so we can all read with confidence. Don’t you feel better? I do. Here, let me try it. Disclosure: Tom is planning to review my book. I feel so clean!
But I did something else the other day too. I actually Googled something about PowerPivot. (I know! A big step for me!) And I found something AMAZING.
The Dreaded Many to Many Problem
When I was at the SharePoint conference, one night I went to Ask the Experts. If you’re a speaker at the conference, they put a tacky t-shirt on you that says EXPERT on it, and then they send waves of people to ask you questions about Reporting Services. Which I know nothing about. I tried to divert them to a real Reporting Services expert, but he was busy with someone else. (Who probably was asking PowerPivot questions).
At some point though I started to get PowerPivot questions. And one guy asked me this:
“We have a lot of many2many relationships in our business. If we adopt PowerPivot, can Excel pros handle many2many?” What’s many to many? Here’s an example:
Each customer likes more than one color, and there is more than one product of each color.
If we sell each customer all of the products from all of the colors that they like,
how much money do we make?
If you try to relate these tables to each other in PowerPivot, using Color as the linkage, you get an error:
You have a bad case of the many to manies. Your prognosis is not good.
You CAN solve this using DAX measures and a few extra steps, but the DAX ends up being kinda scary. At least to me. The Italians aren’t afraid of it – they nail it here and here. And I’ve followed their techniques before – the [link removed due to 404] Retailer Competitive Overlap application uses it. But it wasn’t fun. At all.
So I answered honestly, and told him “no – it’s too complex to grasp for most, even I dread it, and it makes your formulas too complex. M2M is something I encourage people to avoid. It is not the simple part of PowerPivot.”
I don’t even attempt to cover M2M it in the book for instance. It’s spicy scale level 6 in my opinion.
But I got home and started thinking: what if the state of the art has advanced these past few years while I slumbered? And off I went to Google.
Greetings, Gerhard Brueckl!
Here’s the article given to me by Google:
Click the image to read the article
Gerhard is a thinker. He did some serious sleuthing, some reading of Jeffery Wang’s blog, then some thinking, and then some testing. And then probably some more thinking.
He offers up a solution to M2M that I still don’t understand how/why it works. But it’s so much simpler to write that it falls under the heading of “a pattern I can happily re-use many times over.”
Since for now it’s just a pattern to me rather than something I understand at the atomic level, I’ll just try to make it simple here. Please read Gerhard’s article above if you want to understand more deeply.
And if I botch something here, please let me know 
Add JustCustomers and JustColors tables
First you need two new tables. Single column tables that contain only the unique values from the column you’d like to relate on (Color) and from the Column you want to use on rows of your pivot (Customer):
The JustColors and JustCustomers Tables
Relationships
Next, set up relationships so that your model looks like this (orange arrows indicate the direction that filters flow, as always, but in this case it’s harder than usual to understand why the filter flows actually help):
A Simple Four-Table Pattern for M2M in PowerPivot
The Measure
Here’s the magic.
[Potential Sales] =
CALCULATE(SUM(Products[Price]),
‘JustCustomers’,
‘CustomersColorsBridge’,
‘JustColors’
)
What? I just do a simple CALCULATE over a SUM, and then list the names of the three other tables??? (“Other” meaning the tables other than the one where I am performing the SUM).
Well, it works:
A Successful and Simple Many2Many Measure in PowerPivot. Hallelujah.
Listen, I’ve been blogging for three years on this stuff. I’ve even written a book on it. I think it’s a good book. Others think it’s a good book. But this never, EVER, would have occurred to me.
None of us should kid ourselves. This is a deep, DEEP product, this PowerPivot thing, and it still has many magical things lurking to discover.
Well done Gerhard! And well done, Jeffrey, Marco, and Alberto! I love how the state of the art evolves through the interplay of community.
