A Power BI Technique Mined from the Power Pivot Archives
Below, you will find one of our all-time favorite Power BI techniques. In the ten (yes ten) years we’ve been operating this site, we’ve written over 1,000 articles on Power BI. Hard to imagine - even for us - but true.
Years ago, we first wrote up this technique in the context of Power Pivot – Power BI’s cousin and predecessor, which we like to call “Power BI in Excel.”
Since the two products share the same brains (DAX and M), this technique is more relevant today than ever. Everything below is 100% legit for Power BI – the same data model “shape,” the same DAX, etc. – but now you can use it to power up your Power BI visuals, and not just PivotTables. Enjoy!
The Second Time I’ve Used This “Egg on My Face” Picture
OK, on Tuesday I published a technique that I’ve been using for years now – a way to get a list of all measure formulas as text. And it took about five minutes for people to respond and tell me about all the better ways to do it.
I’d like to say that none of those ways were better than mine. But ALL of them were better
David Hager’s Technique: VBA Macro
The most embarrassing “miss” was one that had previously been posted here, ON THIS BLOG, by David Hager. It uses VBA macros to pull a list. You can even download the workbook that does it. Here it is, right here.
I have an excellent memory for certain things. Like, you know, movie quotes. And old stories. Song lyrics. Things that make… connections just kinda “pop” for me. Principles pop for me. 25% of the time, this is a very, very useful talent. And the other 75%, it’s merely a source of amusement.
For most practical things, my memory isn’t very good. Parents on my kids’ soccer teams all seem to learn each others’ names (and mine) within the first three weeks. Not me. They say “hi Rob, how have you been?” and I reply “great, how have you been?” With as much friendly enthusiasm as possible, but completely avoiding their name. It is embarrassing.
Anyway David, my apologies. You’re in good company, which is to say, most everyone I interact with.
Reuvain Krasner’s Technique: Drillthrough Plus
I’m gonna call this technique the most surprising. From the comments on Tuesday’s post:
- Double-click (or right-click > Show Details) on a pivot-table to do a drill-thru.
- Then right-click on the resulting table and select: Table > Edit Query.
- Then replace the command text with:SELECT DISTINCT [Table], [Object], [Expression]
WHERE Object_Type = ‘Measure’
As I read the comment, my first reaction was “this starts with a DRILLTHROUGH? Where is this going?” Wow.
The Vidas (Matelis) Touch: DAX Studio
Get it? The Vidas Touch? Except, his name is pronounced VEE-das, so it doesn’t rhyme with Midas. Like I said, my memory is mostly useful for amusing things.
A long time back, on one of the discussion lists I subscribe to, I saw a million messages go by about something called DAX Studio. The guys talking about it were the Vidas Matelis, Marco Russo, Alberto Ferrari, Chris Webb types – you know, people who were doing the BI thing before I could spell it. Not Excel people really – they work with more sophisticated tools.
So I did what any overworked Excel Pro would do: I completely ignored it. I figured it was something completely standalone and apart from Excel.
Heh heh. Mistake.
It turns out that DAX Studio is a free download, a simple/quick install, AND it’s an Excel addin!
DAX Studio Adds a Single Button to the Add-Ins Tab
Click that button and you get this popup:
DAX Studio Running in Excel, Showing All My PowerPivot Tables
As Vidas instructed, you can do to the DMV tab, drag MDSCHEMA_MEASURES to the query area on the right, and then click the Run Query button:
Three Simple Steps (And No, You Are NOT Supposed to Know What DMV or Anything Like That Means – Don’t Worry About It)
This results in a new sheet tab in your Excel workbook containing the results:
Every Measure in Your PowerPivot Workbook Listed Out:
Name, Formula, Description. Everything. No Special Characters Either!
Much More Than This I Am Sure
There’s a lot more that can be done with DAX Studio but I’m not “in the know” yet. If someone wants to do a guest post on the topic please let me know – I’d be particularly interested in someone explaining it “down” to people like me, because I don’t know MDX etc. (and neither do most readers of this blog).
My Technique Isn’t Going Away Either
My little ZIP/XML file trick still has its place but NOT for listing out your measures. No, the Tuesday trick is not done – it will return in a future post – but I happily concede that I’ve been using it for too many things. Every technique above is better for getting measure lists.