skip to Main Content

power pivot to power bi

A Power BI Technique Mined from the Power Pivot Archives

Below, you will find one of our all-time favorite Power BI techniques. In the ten (yes ten) years we’ve been operating this site, we’ve written over 1,000 articles on Power BI.  Hard to imagine - even for us - but true.

Years ago, we first wrote up this technique in the context of Power Pivot – Power BI’s cousin and predecessor, which we like to call “Power BI in Excel.” 

Since the two products share the same brains (DAX and M), this technique is more relevant today than ever. Everything below is 100% legit for Power BI – the same data model “shape,” the same DAX, etc. – but now you can use it to power up your Power BI visuals, and not just PivotTables.  Enjoy! Smile



“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 original engineering leaders behind Power BI and Power Pivot during his 14-year career at Microsoft, Rob Collie founded a consulting company in 2013 that is 100% devoted to “the new way forward” made possible by Power BI and its related technologies. Since 2013, PowerPivotPro has rapidly grown to become the leading firm in the industry, pioneering an agile, results-first methodology never before seen in the Business Intelligence space. A sought-after public speaker and author of the #1-selling Power BI book, Rob and his team would like to help you revolutionize your business and your career.

This Post Has 17 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?


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

  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.

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

  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?

  7. I want to do what Alex spohe about “Now that they are out, is it possible to export all the measures into a new model, without past each one individually? Thank you”. i am making a bunch of dax formulas in excel and want to get then INTO a power pivot model – any ideas

    1. cool post – and cool comments, everyone! now, really super interested in what gregory and alex asked – how to import dax into a new model instead of pasting it in?

Leave a Comment or Question