Other (Better) Ways to Get All Measures As Text


The Second Time I’ve Used This “Egg on My Face” Picture

Feeling Silly

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 Smile

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 MDX SQL

I’m gonna call this technique the most surprising.  From the comments on Tuesday’s post:

  1. Double-click (or right-click > Show Details) on a pivot-table to do a drill-thru.
  2. Then right-click on the resulting table and select: Table > Edit Query.
  3. Then replace the command text with:

    SELECT DISTINCT [Table], [Object], [Expression]FROM $system.discover_calc_dependency
    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

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!

Every Measure in Your PowerPivot Workbook Listed Out:
Name, Formula, Description.  Everything.  No Special Characters Either!

Download DAX Studio Here.

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 Smile

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.

Rob Collie

One of the founding engineers behind Power Pivot during his 14-year career at Microsoft, and creator of the world’s first cloud Power Pivot service, Rob is one of the foremost authorities on self-service business intelligence and next-generation spreadsheet technology.

This Post Has 14 Comments

  1. Chris Webb

    There’s some interesting stuff that can be done with DAX Studio – basically it allows you to run DAX queries against your PowerPivot model and dump the results into an Excel query table. I did a series of posts on DAX queries last year, all of which is relevant for PowerPivot (although the posts talk about SSAS Tabular):

    Most of what you can do in a DAX query can be accomplished in a PivotTable, but if you wanted more fine-grained control over your data a DAX query could be useful: it would allow you to run queries like “return the top 10 customers for each of my product categories” for example.

    1. powerpivotpro

      Excellent, thanks Chris :)

  2. Ken Puls

    Unfortunately it looks like the days for your little zip/xml trick are numbered Rob. In fact, none of these techniques work in Excel 2013. Based on tests with a workbook I created from scratch in Excel 2013:

    -The CustomXML part that David Hagar was using no longer exists
    -I can’t locate CREATE MEASURE in any of the item#.xml files
    -The Table->Edit on drillthrough is greyed out and not accessible
    -The DAX studio doesn’t recognize the PowerPivot model in an Excel 2013 workbook

    I keep hoping I’m wrong on this, but I’ve spent a long time looking through all the xml components of the file, as well as the VBA object model, and I’m coming up empty handed here. :(

    Ideally, I’m trying to figure out how to list both the measures and the custom columns to a worksheet, but I keep striking out.

  3. Bob Phillips


    I tried the XML trick on 2013 and I found that Edit Query was greyed out also. I would suggest this is a bug and we should raise on Connnect.

    As for DAX studio, is that because the connection is now ThisWorkbookDataModel rather than PowerPivot Data (presumably because xVelocity is now in Excel rather than PP), so maybe DAX Studio just needs updating.

    1. Ken Puls

      Hi Bob,

      I’ll raise it then, and agree that DAX Studio needs an update. The more concerning piece to me is that even in the customXML parts of the file, that information no longer seems to exist. I am trying hard to get at it, and I can’t.

  4. Marco Russo

    There is a nice review of DAX Studio here:

  5. Renato Lyke

    Hi Rob,

    You could use the below method as well to check your Measure dependencies.

    Data- Connections- MAnage Sets – New- Create Set Based on Columns/Rows items – Select you measure – Edit in MDX – will give you the details that your looking for

  6. Frederik


    Here’s another way that only takes 3 mouse clicks.



    1. Ken Puls

      Frederick, thanks for posting this. That still works in Excel 2013, and looks like it has solved my issue. :)

      1. Bob Phillips


        That is the same technique as Reuvain posted, but it works because it is not relying on going through Table>Edit Query, which is grayed out in 2013 for some reason (bug?).

        1. Ken Puls

          I’m seeing that now, Bob. I actually ended up digging through to Chris Webb’s blog and have been playing around with the DMV’s that he listed. I’m now starting to wonder if it was possible to accomplish the goals I had in Excel 2010 after all…

    2. jim

      Thanks Frederik! I was having issues with the DAX Studio method returning too much information. I don’t any SQL, but I figured out that I can paste your query into DAX and retrieve my measures and calculated columns.

      FROM $system.discover_calc_dependency
      ORDER BY 1


  7. geb

    Is there any nifty way that anyone has to import those formulas back into a different model?

    enticingly on the post here:

    was included the promise:

    “But I’ll tell you right now – if you modify this file it will NOT work. Your edits will be completely ignored.

    There’s a workaround for that too of course, muhaha. More on this in a future post.”

    Muhaha searching high and wide.. I haven’t been able to find the follow up.

    1. powerpivotpro

      Well the follow up trick only works in 2010. It no longer works in 2013. But here’s the 2010 trick:

      1) Create a file on your desktop named item1.data – an empty text file that you rename works great
      2) Close Excel
      3) Rename your workbook from .xlsx to .zip
      4) Open up the zip file using Windows Explorer (don’t extract it, just navigate “into” the zip as if it were a folder)
      5) Go into the xl\customData subfolder of the zip
      6) Go back to your Desktop for a moment and copy the “empty” item1.data file
      7) Now paste it into the xl\customData folder of the zip, overwriting the item1.data file that was already there
      8) Close out of the zip file (navigate back up out of it, or close the Explorer window)
      9) Rename the file from .zip back to .xlsx
      10) Next time you load the file into Power Pivot, it will give you an error and ask if you want to try to “recover” the model
      11) Say yes, recover

      At this point, the edits you made to the xml file WILL be used to “re-build” the model. So your edits WILL become reality.

      Lots of gotchas in that, though. Trial and error to make it work. Once you get it working though it’s magic.

Leave a Comment or Question