Been meaning to post this for awhile now.

Here are two error messages I see all the time now, about once per day:

UPDATED:  I’ve found a third message that belongs in this same family, with same root cause and same fix, now included below.

PowerPivot Formula is Invalid Error

PowerPivot Element Not Found Error

PowerPivot The command was canceled error

I typically get the first when I’m adding a measure, the second when I am trying to put a field on a slicer, and the third when I am adding a field from a table I just added to the model.

In my experience, these are all caused by exactly the same thing.

The first error message is completely misleading.  The third is uber-vague.  The second is very much on target.

How to fix this

Don’t worry, your workbook is fine.  All you really have to do is get Excel, the addin, and the PowerPivot db up to date with respect to each other, because something somewhere has gotten out of sync.

There are four fixes you can try, and I recommend you try them in order since each is more time consuming than the previous.  After each step, retry adding the measure (or field) that failed to see if it’s fixed.

    1. Make the pivot update somehow – click a slicer, add a different field, filter it, etc.
    2. Right click a cell in the pivot and choose Refresh
    3. NEW:  Add a calculated column in the PowerPivot window and then delete it (this worked for Maurice Prather where everything else, including tricks 4 and 5, failed)
    4. Save and close the workbook, close Excel completely, reopen workbook
    5. Close Excel, go and clean out the Vertipaq files from your temp folder, reopen workbook

For details on how to find the Vertipaq temp folder, see this entry in the FAQ: