Post by Rob Collie
Seriously, I Want to Hug the Computer and Every Software Engineer in Redmond
Tales from the Preview
I’m traditionally very slow to look at interim releases of software, but the Office 2016 public preview is out. Everything listed below is now also available to you to look at as well. Just go grab the preview and slap it on a “spare” computer.
***UPDATE Oct 2015: The preview period is now closed, but this post will help you find a version of Office 2016 that includes Power Pivot.
Rundown of Improvements
The next release of Excel (2016) brings MAJOR improvements to our world. Unlike 2013, which offered us little noticeable benefit over 2010 Power Pivot, I can’t wait for 2016 to become mainstream. It’s a monstrous win.
Each of these improvements warrants its own in-depth blog post, but for now, let’s just run through the list of things that catch my eye…
Measure Icons and Search in the Field List!
We Had Both of these in 2010. 2013 Took Them Away. 2016 Puts them Back 
A bit cosmetic perhaps, but if you never used 2010 Power Pivot, you have NO IDEA how useful these are. We welcome them back to our world with open arms.
Even Better: Right Click and Edit Measures in Field List!
In the Field List, You Can Now Right Click and Edit a Measure!
(Again, a 2010 Convenience that was ripped from us in 2013)
I can pretty safely say that I will never use the “Manage Measures” dialog box ever again. Introduced in 2013 to compensate us for the loss of right click edit, that thing made me grumpy every time I used it. No more. Or at least, once we’re all using 2016 regularly.
Speaking of Which… “MEASURES” are Back!!!
The Most Amazing Formula Feature In the World is back to Having a Decent Name
(“Calculated Fields” were/are the long-neglected, woefully underpowered feature of traditional pivots)
I’ve sent some EPIC email rants on this topic over the years. Power Pivot measures are a life-changing capability. LIFE-changing, not just game-changing. They will improve your LIFE.
So why, in 2013, were they renamed “Calculated Fields????” That’s the name of the calculation feature in traditional pivots that literally has about 0.00000001 % of the power of Measures.
The 2013 name change, in other words, would scare you off from discovering Measures, the greatest thing ever. Bad idea. It was done in the name of Consistency, and we all know that a foolish consistency is the hobgoblin of software projects.
Excel team, I salute you for reversing course. Plus, we now gain the GOOD kind of consistency, where the same thing is given the same name in all environments. The BI Designer, for instance, calls them Measures. And this makes, you know, books and stuff easier to write too. WIN!
More Readable Data View (in Power Pivot Window)
Small Outlines Around Every Four Rows, Better Contrast, Etc. – Just Much Easier on the Eye
Arrows in Diagram View Point the Correct Direction!
Relationship Arrows Now Indicate the Direction in Which Filters Flow!
(And the “1” and “*” Help Too)
This is another long-running rant, finally put to rest. The number of times I’ve had to explain, “yeah, filters flow in the OPPOSITE of the direction of the relationship arrows…” Boy I won’t miss that little snarky speech.
Calendar/Date Table Creation?
I haven’t even had a chance to try this out yet. But it’s good to see that they are making an attempt to help us do this without having to resort to external machinery.
I Suspect this Still Won’t Be Adequate for Most Needs, But It’s a Start
These Two New Calendar Functions Show More Promise, I Think
(But I’ve Still Yet to Try Them)
More Functions!
Here are the new DAX functions that, so far, excite me the most:
- MEDIANX and PERCENTILEX.INC / PERCENTILEX.EXC – median and percentile calculation has always been tricky in DAX, now we will have first-class functions for them.
- PRODUCTX – no more crazy logarithmic transformations required. This will make forecasting scenarios a LOT easier.
- CONCATENATEX – I am disproportionately excited about this one. If there are five things that tie for best performance, for instance, you can return a list of their names as a Measure in a pivot!
- EXCEPT and INTERSECT – every few months, I find myself wishing I could compare two lists and quickly return just the items that match or don’t match. Usually I then want to perform a calculation on the resulting list. These are going to be super helpful in those cases.
- ISSUBTOTAL – sometimes, HASONEVALUE and HASONEFILTER are subject to exceptional cases like “this parent only has one child,” and as a result, using them to detect whether you’re in a subtotal situation (in a measure) has been only about 99% reliable. This hopefully takes us to 100%.
Um… DAX Variables!!!
This Subtotal Calc Column Used to Require the Use of the EARLIER Function
(But now we can do it with variables in DAX)
Variables DEFINITELY deserve their own post. MULTIPLE posts, actually. But for now, just know that we’ve got this exciting new capability coming to us.
In most cases, you won’t need EARLIER anymore. Wahoo! (Although I have now become comfortable with EARLIER, so this comes a bit late for me).
And just as good, IMO, is that in measures, we won’t constantly be re-calculating, for every cell in the pivot, something that returns the same value every time (like for instance, the first date in the entire calendar table). So there should be some speed gains here too.
Formula Fixup on Rename!
No picture for this, but if you rename a column, all formulas that reference that column will be auto-fixed to use the new name.
Same thing if you rename a measure or a table.
ONE EXCEPTION: if you rename a measure in the Excel window, using the Measure editor that hangs off of pivots, that “slips past the goalie” and does NOT trigger auto-fix. I wonder if the Excel team is aware of this loophole.
Color-coded formula editor
Last but not least: color-based indication of keywords and variables is a nice touch
