The Do’s and Don’ts of PowerPivot – Excel 2016 and SharePoint 2013

When you do development for many years in one platform it gives you the chance to learn a thing or two about all the different limitations across all its different versions. The same rule applies to SharePoint.

Recently our team had to develop in a SharePoint 2013 environment using PowerPivot on a SQL Server 2012 environment.  For about three months our work was going well, until we went from Office 2013 to Office 2016.  Several PowerPivot files began to get an error “Call to Excel Services Returned Error” during their scheduled refresh.  If we opened the file in Excel 2016 and refreshed the file, it refreshed with no issue.

Our team got a hold of the ULS logs and while we could see when the errors occurred and the stack traces, the logs were of little help.  On top of that, hours of googling left no clues.  Ultimately, our team went through a process of elimination to identify the cause of the issues.  Long story short, we whittled down the issue to the DAX formulas used in the PowerPivot files.

We would like to share some do’s and don’ts when creating PowerPivot reports with Excel 2016 and using SharePoint 2013:

Do’s and Don’ts

  1. Do NOT use the IN function ex. (FILTER(‘Table’, [Status ID] IN {1,2}))
    • DO use the OR function instead (FILTER(‘Table’, OR([Status ID]=1,[Status ID]=2)))
  2. Do NOT use the DATEDIFF function. In 2016 you can subtract dates and get negative numbers (future dates), but in 2013 it fails.
    • DO use 1.*([Date 1] – [Date 2]) because it will calculate the days difference without error.
  3. Do NOT change columns from your source data from text to a date field.
    • DO use a surrogate calculated column to create the date column or change the query of the source data to force PowerPivot to cast the data as a date.
  4. Do NOT use the MAX formula on a string. This works in Excel 2016, but does not work in 2013.

Our team suspects these issues are probably due to the differences in the SSAS tabular models used between SQL Server 2012 versus SQL Server 2016.  However, these issues are not well documented for the PowerPivot user and within the self-service BI community.

Hopefully if you are googling this one day, you find this blog article and it helps you.

If you know of other things to avoid in this scenario, or have questions, please feel free to email us at info@definitivelogic.com