Magic: Grab All Your Measure Formulas as Text!



“Daxur an edraith calculatem! Daxur grabbar i formulayen!”

Great Question from the Forums

***UPDATE:  There are at least three better ways to do this, see the followup post HERE.


Another Question from the MrExcel PowerPivot Forums (Link)

In the question above, Mike was asking if there was a way to trace formula dependencies in PowerPivot.  The official answer is no (there may be something if you import your workbook into Visual Studio as a Tabular BISM Project, but I haven’t really been dong that yet.)

But who is content with official answers when there is magic to be had? Smile

How to Get All of Your Measure Formulas as One Text File

1) Save and close your workbook

2) Rename the XSLX (or XLSB/XLSM) to .ZIP




3) Double click to open the ZIP file in Explorer


4) Drill down into the CustomXml folder:


Showing you a bunch of XML files with similar names.  We want the ones named Item1.xml, etc.:


5) Find the biggest of those ItemN.xml files

We actually only care about one of those files. It is USUALLY the largest of the files.


In rare cases you get unclucky and it isn’t the largest, but 95% chance it’s the biggest.

6) Copy that file out of the ZIP and into a “real” folder like your Desktop

7) Open in NotePad, WordPad, Notepad++, etc.


Notepad works if you don’t have a more “sophisticated” text editor

8) Find the text “CREATE MEASURE”



9) Copy every line that starts with CREATE MEASURE into a new text file

And that’s it, you’ve got them Smile

Notes About Certain Symbols

You will see things about your formulas that aren’t quite the same as in the PowerPivot UI.

For instance a greater than symbol “>” will be represented in the text as “>”

In fact any of these symbols will be transformed:  <   >   &   <=   >=

And maybe the | operator as well, but I don’t have an example handy.

Can we modify this file?

Come on, didn’t you see the warning?


Oh, you’re THAT type of person, the kind who ignores warnings?  Yeah, me too Smile

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.

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. ruve1k

    Rob, that is way too much work just to get a listing of all your measure formulas.
    A simpler way:
    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]
    FROM $system.discover_calc_dependency
    WHERE Object_Type = ‘Measure’

    1. KHAN SUL-O

      In 2013 this is not an option at all.

  2. Yuriy

    Thanks Rob, it’s really great and usefull!
    Though point #5 worked for me for the largest file AFTER compession (which is not the largest BEFORE compession).

    ruve1k: your way didn’t work for me, got an error:
    The following syntax error occured during parsing: Invalid token, Line1, Offset 109,’.
    any suggestions?


    1. ruve1k

      There are probably some extra non-printable characters that are getting copied from the HTML of the post. Try deleting all of the spaces and then typing them in yourself.

      1. Yuriy

        Thanks ruve1k, got it! (had to re-type single quotes ‘)

  3. ruve1k

    To help trace dependencies you can just pull in the columns beginning with REFERENCED_

    See Chris Webb’s great blog post:

  4. Vidas M

    Another way to get all measures is:
    – Install DAX Studio (bing it)
    – In Excel go to DAX Studio add-in, select DMV tab and drag MDSCHEMA_MEASURES into query area, click execute.

    You will get nicely formatted excel workbook with all measures, descriptions, formulas, etc.

    1. Colin

      Wow, getting easier all the time :-) Thanks.

    2. Mike D

      Even better yet, you can use DAX Studio in conjunction with the information from Chris Webb’s blog post that ruve1k provided to directly pull a table of the dependencies.

      Substitute MDSCHEMA_MEASURES with DISCOVER_CALC_DEPENDENCY. Then you get a detailed table of all measures and their dependencies.

  5. David Hager

    Or you can use my original method for this that was published on powerpivotpro a long time ago – much more versatile.

    1. powerpivotpro

      Hey David what was the link? I’ll update.

  6. David Hager

    Your e-mail is not working, and I have not been able to post the URL here. However, the title was “Catalog your measures with a nifty macro”.

  7. Alex

    Now that they are out, is it possible to export all the measures into a new model, without past each one individually? Thank you.


  8. Amit

    For some reason I followed the steps and got measures that aren’t mine :(

    I found them on the 2nd largest file (not the first as you said normally is).

    Couldn’t find my measures anywhere… any idea why and how to deal with it?

Leave a Comment or Question