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.