“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. 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’

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


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

  4. 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”.

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


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