Magic: Grab All Your Measure Formulas as Text!

 

image

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

image

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

image

Yielding…

image

3) Double click to open the ZIP file in Explorer

image

4) Drill down into the CustomXml folder:

image

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

image

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.

image

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.

image

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

8) Find the text “CREATE MEASURE”

image

image

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?

image

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.

12 Responses to Magic: Grab All Your Measure Formulas as Text!

  1. ruve1k says:

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

    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?

    Thanks

  3. ruve1k says:

    To help trace dependencies you can just pull in the columns beginning with REFERENCED_
    http://msdn.microsoft.com/en-us/library/gg471590%28v=sql.110%29.aspx

    See Chris Webb’s great blog post:
    http://cwebbbi.wordpress.com/2011/09/17/documenting-dependencies-between-dax-calculations/

  4. Vidas M says:

    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.

    • Colin says:

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

    • Mike D says:

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

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

  6. David Hager says:

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

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

    Alex

Leave a Comment or Question