I was working with a client last week and the unthinkable happened. The client made a simple editing mistake inside the Calculated Field editing dialogue box and it corrupted the Power Pivot workbook. We could still edit the corrupt workbook, but the Pivot Tables stopped working. Oh No!! Now what? He had written half a dozen new Measures (Calculated Fields) and all that effort now seemed to be lost.
Luckily we had incremental backups of the workbook we were developing. Well actually it wasn’t luck – it was good forward planning. Even though Power Pivot is an excellent tool, anyone that uses it will know it is a bit flaky and it can cause problems when editing. As a precaution I always keep incremental backups of my workbooks when doing data modelling work. I have optimised this process using an incremental backup macro I created. You can read about that here and even download the VBA code to use yourself.
OK, but we still had a problem. We had a backed up copy of the workbook, but this backed up copy didn’t contain the last 30 mins work – about half a dozen DAX formulae. Power Pivot doesn’t allow you to export the Measures (Calculated Fields) and then reuse them. But there is a solution using DAX Studio.
What is DAX Studio?
DAX Studio is a great tool that allows you to do many things with your Excel Power Pivot data models. It will allow you to use DAX as a query language to interrogate your data model and many other things. One of those “other things” it allows you to do is get a list of all of your DAX Measures in a new sheet in your workbook.
You can read more about DAX Studio and download it here. Note I installed version 2.2.1 today and it didn’t work for me. I am currently using version 2.1.1 and that works fine.
Step by Step Instructions on how to Extract Calculated Fields
This is how you go about extracting your calculated fields from an Excel Workbook.
First you need to install DAX Studio. Just download it from the link above. Don’t run DAX Studio when prompted to do so immediately after the install. Just exit after the install is finished. Make sure you have Excel closed when you run the install.
The next step is to open Excel, open your work book and then run DAX Studio. You will find DAX Studio on the Add-Ins tab on the Excel Ribbon.
When you launch, it will ask you which Data Source you want to connect to. In this demo I am connecting to a data model in an Excel workbook. This is what most Excel users will normally do. It will also work with a tabular instance of SSAS if you have these.
Once connected, you will see a list of tables in your Power Pivot Data model on the left. The next step is to switch to the DMV tab down the bottom of the screen.
When you have clicked on the DMV tab, you will see a list of objects in the left hand panel. Locate the one called MDSCHEMA_MEASURES and drag it into the editing pane on the right.
DAX Studio will then automatically write the query required to extract the Measures from the workbook.
Before executing the query you should change the output settings so it sends the results to a clean spreadsheet within your workbook. When done, click the Run button to execute the query.
After you have run the query, you will notice there is a new sheet in your workbook that contains all the detail of your calculated fields/measures.
But there is a lot of information here that you probably don’t care about. You can manually clean up the data by deleting what is not important. You really just need the name and the formula (columns D and N) and maybe a couple of others. I have written a small VBA macro to clean up the DAX Formatter output for this exercise. Feel free to use this code if you like. If you don’t know how to use this VBA code ,you can read about how to do that here.
Sub CleanDAXStudioMeasures() Application.ScreenUpdating = False Columns("A:B").Delete Shift:=xlToLeft Columns("C:D").Delete Shift:=xlToLeft Columns("C:I").Delete Shift:=xlToLeft Columns("D:G").Delete Shift:=xlToLeft Columns("E:E").Delete Shift:=xlToLeft Columns("C:C").ColumnWidth = 50 Range("A1").Select While Selection.Value <> "" If Left(Selection.Value, 1) = "$" Then Selection.EntireRow.Delete Else If Left(Selection.Offset(0, 1).Value, 1) = "_" Then Selection.EntireRow.Delete Else Selection.Offset(1, 0).Select End If End If Wend Range("A1").Select Selection.EntireColumn.Delete Range("C1").Value = "Table" Range("E1").Value = "Full Formula" ActiveSheet.ListObjects.Add(xlSrcRange, Range("A1").CurrentRegion, xlYes).Name = "Table1" Range("E2").FormulaR1C1 = "=RC[-4]&"":=""&RC[-3]" Columns("A:E").EntireColumn.AutoFit Application.ScreenUpdating = True End Sub
After running the above Macro, you will end up with a table that contains the relevant information including the following full formulae in the last column.
You can simply cut and paste these formula one at a time into the calculation area at the bottom of each table inside the Power Pivot window of you last backed up copy of the workbook. Note you will then need to manually apply any data formatting you need.