(Image Courtesy of Orbitz.com) Disproportionately Popular International Destinations by US State – Can We Do Something Similar? Inspiration Strikes! I haven’t done a DAX post in a long time, so I was thrilled to get some inspiration from an unlikely…
Post by Rob Collie
Does Your Pivot Look Like This? Does its Slow make you Sad? Time for a Fix!
Tell me if this sounds familiar…
Yes, you know that pivots are meant to show aggregations. Summaries. Pivots were NOT invented to display thousands of rows of detail data.
But still, sometimes you need to do precisely that. The biz needs its list of customers and how much they’ve been buying, for instance, and all that data is in YOUR Power Pivot model.
And hey, pivots are really the only game in town* for table-shaped display of data. So, you build one of the monstrosities like the above.
(*OK yeah, you DO know about this thing called DAX Query Tables, but those are seriously a pain to set up. So, no. You rule those out before even starting. Just like me!
So You Do The Flattened Pivot Dance, Right???
In pictorial form…
The Flattened Pivot Option – Found in the PivotTable Dropdown in the Power Pivot Window
Next, You Pile a Whole Bunch of Fields Onto Rows
Then, on the PivotTable Design Ribbon Tab, You Turn Off All Subtotals
And Voila! It’s Slow as Heck.
Is this possible?
Someone at Microsoft asked me this question the other day:
“Sort of like how you’ve used a slicer for conditional formatting, is it possible to use a slicer to change the custom formatting of a number? In my use case, I want to be able to display currency as either full number ($1,500,000.00) or abbreviated ($1.5M) as the viewer wishes. See below for an example of the desire.”
Can We Do This in Power Pivot?
My Answer: No, not possible. Wait, maybe. Hmm. OK, yes, mostly.
All of these thoughts flashed before my eyes:
- Power Pivot measures/calc fields must always have a consistent data type. You can’t have a measure return numbers sometimes and text other times, for instance. All “exits” from an IF or a SWITCH must have the same data type.
- Apparently, #1 is no longer true in SSAS Tabular, in the 2014 release. They now support “variant” data type measures.
- But no, Power Pivot still lacks that “variant” measure capability, at least for now.
- Whoa, hold on a second. The desired result above does NOT use different data types! It’s all numbers! So we just need to change the math!
- Oh, ouch, not so fast. The “M” and the “K” – I don’t know how to add those labels in a numerical data type.
So this means… text measures!