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