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


The Second Time I’ve Used This “Egg on My Face” Picture

Feeling Silly

OK, on Tuesday I published a technique that I’ve been using for years now – a way to get a list of all measure formulas as text.  And it took about five minutes for people to respond and tell me about all the better ways to do it.

I’d like to say that none of those ways were better than mine.  But ALL of them were better Smile

David Hager’s Technique:  VBA Macro


The most embarrassing “miss” was one that had previously been posted here, ON THIS BLOG, by David Hager.  It uses VBA macros to pull a list.  You can even download the workbook that does it.  Here it is, right here.

I have an excellent memory for certain things.  Like, you know, movie quotes.  And old stories.  Song lyrics.  Things that make… connections just kinda “pop” for me.  Principles pop for me.  25% of the time, this is a very, very useful talent.  And the other 75%, it’s merely a source of amusement.

For most practical things, my memory isn’t very good.  Parents on my kids’ soccer teams all seem to learn each others’ names (and mine) within the first three weeks.  Not me.  They say “hi Rob, how have you been?” and I reply “great, how have you been?”  With as much friendly enthusiasm as possible, but completely avoiding their name.  It is embarrassing.

Anyway David, my apologies.  You’re in good company, which is to say, most everyone I interact with.

Reuvain Krasner’s Technique:  Drillthrough Plus MDX SQL

I’m gonna call this technique the most surprising.  From the comments on Tuesday’s post:

  1. Double-click (or right-click > Show Details) on a pivot-table to do a drill-thru.
  2. Then right-click on the resulting table and select: Table > Edit Query.
  3. Then replace the command text with:SELECT DISTINCT [Table], [Object], [Expression] FROM $system.discover_calc_dependency
    WHERE Object_Type = ‘Measure’

As I read the comment, my first reaction was “this starts with a DRILLTHROUGH?  Where is this going?”  Wow.

The Vidas (Matelis) Touch:  DAX Studio

Get it?  The Vidas Touch?  Except, his name is pronounced VEE-das, so it doesn’t rhyme with Midas.  Like I said, my memory is mostly useful for amusing things.

A long time back, on one of the discussion lists I subscribe to, I saw a million messages go by about something called DAX Studio.  The guys talking about it were the Vidas Matelis, Marco Russo, Alberto Ferrari, Chris Webb types – you know, people who were doing the BI thing before I could spell it.  Not Excel people really – they work with more sophisticated tools.

So I did what any overworked Excel Pro would do:  I completely ignored it.  I figured it was something completely standalone and apart from Excel.

Heh heh.  Mistake.

It turns out that DAX Studio is a free download, a simple/quick install, AND it’s an Excel addin!



DAX Studio Adds a Single Button to the Add-Ins Tab

Click that button and you get this popup:

DAX Studio Running in Excel, Showing All My PowerPivot Tables

DAX Studio Running in Excel, Showing All My PowerPivot Tables

As Vidas instructed, you can do to the DMV tab, drag MDSCHEMA_MEASURES to the query area on the right, and then click the Run Query button:


Three Simple Steps (And No, You Are NOT Supposed to Know What DMV or Anything Like That Means – Don’t Worry About It)

This results in a new sheet tab in your Excel workbook containing the results:

Every Measure in Your PowerPivot Workbook Listed Out - Name, Formula, Description.  Everything.  No Special Characters Either!

Every Measure in Your PowerPivot Workbook Listed Out:
Name, Formula, Description.  Everything.  No Special Characters Either!

Download DAX Studio Here.

Much More Than This I Am Sure

There’s a lot more that can be done with DAX Studio but I’m not “in the know” yet.  If someone wants to do a guest post on the topic please let me know – I’d be particularly interested in someone explaining it “down” to people like me, because I don’t know MDX etc. (and neither do most readers of this blog).

My Technique Isn’t Going Away Either Smile

My little ZIP/XML file trick still has its place but NOT for listing out your measures.  No, the Tuesday trick is not done – it will return in a future post – but I happily concede that I’ve been using it for too many things.  Every technique above is better for getting measure lists.

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 16 Comments
  1. There’s some interesting stuff that can be done with DAX Studio – basically it allows you to run DAX queries against your PowerPivot model and dump the results into an Excel query table. I did a series of posts on DAX queries last year, all of which is relevant for PowerPivot (although the posts talk about SSAS Tabular):

    Most of what you can do in a DAX query can be accomplished in a PivotTable, but if you wanted more fine-grained control over your data a DAX query could be useful: it would allow you to run queries like “return the top 10 customers for each of my product categories” for example.

  2. Unfortunately it looks like the days for your little zip/xml trick are numbered Rob. In fact, none of these techniques work in Excel 2013. Based on tests with a workbook I created from scratch in Excel 2013:

    -The CustomXML part that David Hagar was using no longer exists
    -I can’t locate CREATE MEASURE in any of the item#.xml files
    -The Table->Edit on drillthrough is greyed out and not accessible
    -The DAX studio doesn’t recognize the PowerPivot model in an Excel 2013 workbook

    I keep hoping I’m wrong on this, but I’ve spent a long time looking through all the xml components of the file, as well as the VBA object model, and I’m coming up empty handed here. 🙁

    Ideally, I’m trying to figure out how to list both the measures and the custom columns to a worksheet, but I keep striking out.

  3. Ken,

    I tried the XML trick on 2013 and I found that Edit Query was greyed out also. I would suggest this is a bug and we should raise on Connnect.

    As for DAX studio, is that because the connection is now ThisWorkbookDataModel rather than PowerPivot Data (presumably because xVelocity is now in Excel rather than PP), so maybe DAX Studio just needs updating.

    1. Hi Bob,

      I’ll raise it then, and agree that DAX Studio needs an update. The more concerning piece to me is that even in the customXML parts of the file, that information no longer seems to exist. I am trying hard to get at it, and I can’t.

  4. Hi Rob,

    You could use the below method as well to check your Measure dependencies.

    Data- Connections- MAnage Sets – New- Create Set Based on Columns/Rows items – Select you measure – Edit in MDX – will give you the details that your looking for

      1. Ken,

        That is the same technique as Reuvain posted, but it works because it is not relying on going through Table>Edit Query, which is grayed out in 2013 for some reason (bug?).

        1. I’m seeing that now, Bob. I actually ended up digging through to Chris Webb’s blog and have been playing around with the DMV’s that he listed. I’m now starting to wonder if it was possible to accomplish the goals I had in Excel 2010 after all…

    1. Thanks Frederik! I was having issues with the DAX Studio method returning too much information. I don’t any SQL, but I figured out that I can paste your query into DAX and retrieve my measures and calculated columns.

      FROM $system.discover_calc_dependency
      ORDER BY 1


  5. Is there any nifty way that anyone has to import those formulas back into a different model?

    enticingly on the post here:

    was included the promise:

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

    Muhaha searching high and wide.. I haven’t been able to find the follow up.

    1. Well the follow up trick only works in 2010. It no longer works in 2013. But here’s the 2010 trick:

      1) Create a file on your desktop named – an empty text file that you rename works great
      2) Close Excel
      3) Rename your workbook from .xlsx to .zip
      4) Open up the zip file using Windows Explorer (don’t extract it, just navigate “into” the zip as if it were a folder)
      5) Go into the xl\customData subfolder of the zip
      6) Go back to your Desktop for a moment and copy the “empty” file
      7) Now paste it into the xl\customData folder of the zip, overwriting the file that was already there
      8) Close out of the zip file (navigate back up out of it, or close the Explorer window)
      9) Rename the file from .zip back to .xlsx
      10) Next time you load the file into Power Pivot, it will give you an error and ask if you want to try to “recover” the model
      11) Say yes, recover

      At this point, the edits you made to the xml file WILL be used to “re-build” the model. So your edits WILL become reality.

      Lots of gotchas in that, though. Trial and error to make it work. Once you get it working though it’s magic.

  6. Hi Rob,

    For some reason it seems like your zip method is the only one, which is available/and working at this site. I have files with 100’s of measures,that we need to combine into a new and larger model.

    But when I Extract the Item files a few measures that is not in the model at all shows up. Can that be old and deleted measures from my predecessor. Or is it possible that hidden measures are shown when I look at the item file?

  7. Private Sub PrivExtractMeasures()
    Dim measure As ModelMeasure

    Application.DisplayAlerts = False
    For Each ws In ActiveWorkbook.Worksheets
    If ws.Name = “MeasureTable” Then
    End If
    Next ws
    Application.DisplayAlerts = True

    Worksheets.Add ActiveSheet
    ActiveSheet.Name = “MeasureTable”

    ActiveCell.value = “Measure”
    ActiveCell.Offset(, 1).value = “Formula”
    For Each measure In ActiveWorkbook.Model.ModelMeasures
    ActiveCell.value = measure.Name
    ActiveCell.Offset(, 1).value = measure.Formula
    Next measure

    Columns(“B:B”).ColumnWidth = 100

    ActiveSheet.ListObjects.Add(xlSrcRange, Range(Cells(1, 1), Cells(ActiveCell.Row, 2)), , xlYes).Name = _

    End Sub

Leave a Comment or Question