Other (Better) Ways to Get All Measures As Text

 
image

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

image

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!

 

image

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:

image

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.

12 Responses to Other (Better) Ways to Get All Measures As Text

  1. Chris Webb says:

    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):
    http://cwebbbi.wordpress.com/2011/07/13/dax-queries-part-1/
    http://cwebbbi.wordpress.com/2011/07/15/dax-queries-part-2/
    http://cwebbbi.wordpress.com/2011/07/18/dax-queries-part-3/
    http://cwebbbi.wordpress.com/2011/07/27/dax-queries-part-4/
    http://cwebbbi.wordpress.com/2011/08/06/dax-queries-part-5/
    http://cwebbbi.wordpress.com/2011/08/10/dax-queries-part-6/

    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.

  2. Ken Puls says:

    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 says:

    Ken,

    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.

    • Ken Puls says:

      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. Renato Lyke says:

    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

  5. Frederik says:

    Bob,

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

    http://www.vandeputte.org/2012/02/powerpivot-nuggets-part-14-generate.html

    Frederik

    • Ken Puls says:

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

      • Bob Phillips says:

        Ken,

        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?).

        • Ken Puls says:

          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…

    • jim says:

      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.

      SELECT DISTINCT [TABLE], OBJECT_TYPE, OBJECT, EXPRESSION
      FROM $system.discover_calc_dependency
      WHERE OBJECT_TYPE = ‘MEASURE’
      OR OBJECT_TYPE = ‘CALC_COLUMN’
      ORDER BY 1

      :)

Leave a Reply