Reports Without Direction Can be “Noisy” Welcome back readers, to another chapter in the DAX Reanimator Series! The post I’ll be re-envisioning today is one I’m very excited about. It’s an article that does some really cool magic using DAX,…
Welcome back P3 Nation for Part 2 of our Power Query (M)agic series! Today I’ll be addressing an issue many of us have experienced, what to do with the LAUNDRY LIST of PQ queries you have. Since it feels really…
Data IS Life…and “Life, Uh, Finds A Way.” P3 friends…for those of you in the know (and for those who aren’t), Power Query (or PQ for short) is one of the cornerstones of the Microsoft BI Suite. It’s a tool…
(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…
Have you Heard This Joke: Gil, Austin, and Rob (left to right) Walk Into a Bar…(Yes, Rob’s hair was purple at the time.)(Note from Rob: and it’s still purple today!) Intro from Rob Well folks it’s an exciting time to…
This Email and the Highlighted Text were Automatically Generated in Response to DAX “Detectors” Scanning Our Results During Scheduled Refresh I’ve Wanted this Feature Forever. We Now Have It. Classes Announced for Houston, Los Angeles, Indianapolis, and London First, we…
Out of 407 Total Combinations of Subcategory and Region, These 8 Stand Out This Month (These 8 Combinations Differed GREATLY from their Respective 12-month Averages) A Post Long in the Thinking: Formulas that “Scan” (What’s this, you say? An actual…
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.
by Matt Allington I have learnt a lot over the last year working full time as a Power Pivot professional. And some of the things that I dismissed as “not useful” very early on have turned out to be very…
Post by Rob Collie
Did Someone Say Deliberately “Misuse” a DAX Function for Our Benefit? We’re IN!
An End to Nested IF’s? Sign Us Up!
When we first saw the SWITCH function make its debut in Power Pivot a few years back, it was a “hallelujah” moment.
Whereas we used to have to write nested IF’s, such as this:
Now , with SWITCH, we could write that much more cleanly as:
Which do you prefer? It’s easy to make a strong “case for SWITCH,” isn’t it?
But What About Cases Other than Equals?
Now, let’s consider the following nested IF:
Notice that we’ve swapped out “=” for “<”.
And we can’t do that as a SWITCH, because SWITCH checks for exact matches between [Measure1] and 1 (or 2, 3, etc.)
This is unfortunate, because in these cases, we’ve had to keep using nested IF’s. And wow do I (Rob) *hate* nested IF’s. I can never seem to match the parentheses up correctly on the first try.