PowerPivot measures command – no really, don’t modify it manually!

If you look in the metadata of a BIM file, or if you look at the .dax file generated by the DAX Editor, you will see the following perplexing comment:

 ----------------------------------------------------------
-- PowerPivot measures command (do not modify manually) --
----------------------------------------------------------

This is actually a meaningful comment. Despite having PowerPivot in the name, it is used in the tabular designer in Visual Studio as well. This header is used to tell the measure grid where the measures are in the model.

If you modify the header manually, here is what will happen. Say you insert a carriage return between the <Text> and the first hyphen in the comment. When you reopen the designer, you will see that all of your measures are gone from the measure grid! The measures are still in the model, and in fact when you deploy to the server you can query the measures perfectly fine. The designer simply cannot see the measures because the text does not match exactly the text generated by the designer.

I found this quirk of design not by hand-editing my BIM file (which is bad), but when processing my .asdatabase file as shown in my msbuild customization example. PowerShell, not understanding that it could not treat the .asdatabase file as any other XML file, inserted line breaks. When I deployed the database, everything seemed fine. However, next time I created a new tabular project by importing my model, the measures were all gone from the database. In a panic (thinking my demo ruined!) I filed a high priority bug and got a bunch of people to gather round and help me with my conundrum. And as it turns out, I just had to delete one line break from the XMLA, and my model was restored.

Bottom line: that command above is not joking. Do not modify the PowerPivot measures command manually. Consider yourself warned.