A2- So Much Power, So Little Space: Further Capabilities
My original estimate for this book was 150 pages. Well it’s turned out quite a bit longer than that, and yet the list of things I don’t have space for is still quite sizeable.
I want to at least make you aware of these topics though, and in some cases point you to resources that I think could be helpful.
The first two of these, Power View and Cube Formulas, are the ones I most wish I’d had space for. That said, each of these has already been the subject of multiple entire books. So it was just a sensible decision to leave them out.
The rest of these don’t feel so bad. None of them are things I use today. Maybe in the future I will, but I think it’s fair to categorize them as “you can be extremely effective at PowerPivot long before you need them.”
Power View
Power View is a new visualization surface (a new sheet type!) in Excel 2013 that can be used to display all kinds of modern stuff like maps, card views, and animated charts. (The 2013 version of Power View is actually Power View V2. V1 is not built-in to Excel, and exists purely as a SharePoint feature).
There’s at least one post about Power View on the blog. This one shows off a few capabilities of Power View V1:
Cube Formulas
Any pivot you create with PowerPivot can be completely “exploded” into formulas – each cell in the pivot becomes a cube formula. This opens up limitless formatting possibilities, interaction between DAX and Excel’s in-sheet calc engine, and some really creative things like the calendar chart.
There’s actually quite a bit about cube formulas on the blog, under the cube formulas category:
http://ppvt.pro/CubeFormulasCat
GENERATE(), SUMMARIZE(), CALCULATETABLE(), ADDCOLUMNS(), KEEPFILTERS(), ROLLUP(),
CROSSJOIN()
There are a lot of functions that help you work with entire “virtual” tables behind the scenes during a measure calculation.
I’ve yet to truly even attempt using them. David Churchward has used a few of these in guest posts to the blog, but really, this is a place where I recommend the Italians – Russo and Ferrari, plus our English friend Chris Webb. I believe they have a new book in the works for Excel 2013 and it’s going to cover a lot of these more advanced things in great detail.
TOPN()
This one is new in PowerPivot V2 and I think I’m going to be using it a lot, but I have not had much time with it yet. It looks like a version of FILTER() that makes it easy to just return the top n rows. So you’d use this as a <filter> argument in a CALCULATE().
Hierarchies
The ability to link several fields in the field list together, so that all get added at the same time and in the proper order (think Country/State/City).
This feature doesn’t interest me much, to be honest, since the vast majority of consumers don’t ever want to see a field list, and this feature is really only useful for the person who is using the field list.
PATH() Functions
That said there are a number of PATH() functions added to DAX that all work with hierarchies, so there might be some usefulness to hierarchies yet :-)
HASONEFILTER(), ISFILTERED(),
ISCROSSFILTERED()
The first two make semantic sense to me and should be easy to decipher. The third is still quite foreign to me.
USERELATIONSHIP()
PowerPivot V2 allows multiple relationships to exist between a pair of tables, whereas V1 restricted you to a single link. USERELATIONSHIP() lets you select which linkage you use in a formula, in the event there is more than one.
The multiple link feature is intended for cases such as where you have one Calendar table but two date columns in your Sales table – like OrderDate and ShipDate. Sometimes you want to show Sales data on your pivot according to order date, and other times you want to see it via ship date.
In the past you’ve had to have two Calendar tables to deal with that situation. It’s unclear to me as of yet whether the USERELATIONSHIP() formula tradeoff will be valuable in my work, or whether I will keep using two different tables.
“Many to Many” Relationship Scenarios
Sometimes you have a pair of tables that you’d ideally like to relate, but it’s not possible because there are duplicate values in each table. There is a DAX formula “workaround” for this situation however, but it is quite complicated.
Actually, it’s not so much complicated as it is hard to understand. I have a few workbooks that use this technique, but I copied the technique from the Italians :-) I was able to modify the pattern to fit my needs but this technique strikes me as “not something Excel Pros need to learn in their first year of PowerPivot.” Read Ferrari/Russo/Webb for more on this.