Catalog Your Measure with a Nifty Macro

User Rating: 5 / 5

Star ActiveStar ActiveStar ActiveStar ActiveStar Active

When defined name formulas are added to an Excel workbook, they can be easily accessed and viewed through the use of Excel's Name Manager. However, when measures (formulas added to PowerPivot pivot tables and based on the DAX query language) are created, there is no way to know that they exist except for visually scanning the PowerPivot field list. An icon resembling a calculator will be to the right of each formula created in the field list. However, in order to view the DAX formula, the field must be right-clicked and "Edit Measure" selected. Then, if you wanted to store that measure somewhere for future reference, it can be copy/pasted to the desired location. This process becomes time-consuming if you have many PowerPivot workbooks that contain many measures. It would be nice if that process could be automated. To that end, the following VBA procedure for opening files in a folder and extracting the DAX measures and putting them in a table can be downloaded here.


Tags: excel, script


2007-2015 VidasSoft Systems Inc.