Changing how PowerPivot handles DAX errors

You can change how PowerPivot handles DAX errors by changing the connection string's "Cell Error Mode" parameter value. There are 4 possible values for the "Cell Error Mode" connection string parameter:

• TextValue – when encountering an error, show the error message as a string value for the cell, and stop returning further results. New created PowerPivot workbooks will use this value by default.
• Cell – the SSAS 2005/2008 behavior MDX, return individual errors for each cell. With this setting in Excel you will see "#VALUE!", but you will not see actual error message.
• Query – fail the entire query, without returning any axis/cell results.
• Default (or when the property wasn’t specified) – maps to ‘Cell’ for MOLAP/MDX, and ‘Query’ for Vertipaq/DAX.

By default a PowerPivot workbook connection string will have the "Cell Error Mode" property value set to "TextValue". In this configuration you can see the actual error message, and you can see the cell where the first error occurred. But most likely you will see just a few cells in the pivot as results will stop returning after the first encountered error.

Changing the "Cell Error Mode" property value to "Cell" will show you values for all pivot table cells and cells that raised an error will have the value "#VALUE!". With this setting you will not be able to see the actual error message in Excel user interface.

Changing the "Cell Error Mode" property value to "Query" will prevent you from adding calculated measure to pivot when its formula raises error for any cell. In such case you will see a pop up message with error text and your existing pivot will not be updated.

How to change connection string properties for PowerPivot workbook

You can change the connection string properties for PowerPivot in the same way as for other Excel connections.

In Excel go to the "Data" pane and select "Properties". In the "Connection Properties" window go to the "Definition" tab. There you will see a connection string that will look similar to:

Provider=MSOLAP.4;Persist Security Info=True;Initial Catalog=Microsoft_SQLServer_AnalysisServices;Data Source=$Embedded$;MDX Compatibility=1;Safety Options=2;MDX Missing Member Mode=Error;Optimize Response=3;Cell Error Mode=TextValue

 

The last parameter "Cell Error Mode" sets how DAX errors are handled by PowerPivot.

Demo PowerPivot workbook

Lets create a sample PowerPivot workbook with a DAX calculation that would raise an error and see how this error is handled with a different "Cell Error Mode" value.

Lets say in Excel we have a table named "Sales":

We can load this table into PowerPivot as a Linked table:

Now lets create a simple pivot report where in the row area we add "Currency" and in the Values area we add "Sales Amount":

Now let's create a simple DAX formula for the CurrencyRate calculated measure. For this test we will use rate 1.0 for the "US$" currency and we will use the rate 0.9 for all of the other currencies. Also for this demo we will assume that in the pivot report we will have currency in the row or column area, so we can deal with a single currency value. Our DAX formula will be:

=IF(VALUES(Sales[Currency]) = "US$", 1.0. 0.9)

 

In our DAX formula we use the VALUES function that returns a table with a list of unique Currency values in the current context. In our formula we expect that the VALUES() function will return just one Currency value, so we can compare that value to a string "US$".

But there is an obvious flaw in this formula - if the current formula context returns more than one currency record, our DAX calculation will fail as PowerPivot will not be able to compare a table with a string.

PowerPivot behavior with "Cell Error Mode" set to "TextValue"

When we created new PowerPivot connection, by default "Cell Error Mode" property was set to "TextValue". Lets test how DAX errors are handled in this configuration.

When we add our calculated measure "CurrencyRates" to the  Pivot table, we get following error message in one of the the cells where we expected our calculated measure value:

ERROR - CALCULATION ABORTER: MdxScript(Sandbox)(5,51) A table of multiple values was supplied where a single value was expected

Here is the screenshot of this error:

Based on this error we can understand that our DAX formula does not properly handle cases when multiple currency values are encountered and we can see that error is raised for row "Grand Total".

PowerPivot behavior with "Cell Error Mode" set to "Cell"

Now let's change the connection string property "Cell Error Mode" from the default value of "TextValue" to "Cell". We can see that in such an environment values for cells that do not raise error are visible and just one cell that raises an error has value of "#VALUE!". But with this setting there is no detailed error message that could help you understand why your DAX formula is failing.

In our case we know from before that we were not properly handling multiple currency values and from the screenshot below we can see that error is in the "Grand Total" calculation:

When the "Cell Error Mode" value is set to "Cell", then the error handling behavior is similar to the error handling behaviour of SSAS MDX and will probably be favored by SSAS developers. But if you work with large PowerPivot tables, you should keep in mind that it might take a long time to get the result of many cell errors.

Another problem with this setup is that if you will disable "Grand Total" calculation in Excel, it will apear that all cell are calculated properly and you will not be able to notice that there is a hole in your DAX formula logic.

PowerPivot behavior with "Cell Error Mode" set to "Query"

Now let's try to set the property "Cell Error Mode" value to "Query". The screenshot below shows what will happens when you will try to change the property value from "Cell" to "Query". As soon as you make a change, you will receive a pop up error message:

"Data could not be retrieved from the external data source.
Error message returned by the external data source:
MdxScript(Sandbox) (5,51) A table of multiple values was supplied where a single value was expected."

Here is a screenshot of this message and behind the pop up message you can see a pivot table that has values as they were before the attempted change.

Also, when you receive the above error message, your connection string property will be reset back to what it was before - in our case back to the value "Cell".

If we will remove our calculated measure from the Pivot, then we can change the "Cell Error Mode" property value to "Query" and save that setting. But now when you will try to add calculated measure "CurrencyRate" back to Pivot, you will get an error message that is a bit different:

"Could not add the field "CurrencyRate" to the PivotTable because the formula is invalid"

Here is the screenshot of this message and again behind the pop up window you can see that the Pivot table was not changed.

So with the "Cell Error Mode" property set to "Query"  we saw that the Pivot table is not changed if the DAX formula generates an error and you can see the error in a pop up window, but the actual error description is not always detailed enough.

Special case - "Cell Error Mode" set to "TextValue" and we do not show the grand total value

I would like to mention a special case when the "Cell Error Mode" property value is set to "TextValue" and we do not show the grand total value in our pivot, but the DAX formula fails on Grand total calculation. Here is a screenshot of how our Pivot table result will look like in such setup:

As you can see in the screenshot above, the error message is not visible but also you do not see any values for other measures. In this case MDX query against PowePivot includes calculation for Grand Total row that raises error, but that cell with error is never displayed in Excel, so you do not see any error in Excel user interface. This scenario could be confusing and in such a case I would recommend to enable Grand totals for your pivot table and disable them just after you fix all of the DAX errors. You might also experience similar behaviour when you are not properly handling UnknownMember value in your DAX formula.

DAX Formula fix

Now lets update our DAX formula for "CurrencyRate" measure so it could handle cells where multiple Currencies would be possible:

=IF(COUNTROWS(VALUES(Sales[Currency])) <> 1, BLANK(), IF(VALUES(Sales[Currency]) = "US$",1.0,0.9))

In this updated formula we check how many different currency values there are in the current context and if the count is not 1, we set the result to blank value.

Note: Even with this fix, handling currency rates by hardcoding them into a DAX formula is a very bad idea and was used here just for demo purposes.

DAX numeric calculation errors

When PowerPivot DAX encounters a numeric error like division by zero or overflow, then these errors are not handled by DAX, but these values are just passed through the system as any other number. So in such case property "Cell Error Mode" settings do not affect numeric calculation errors handling.

Summary

Default "Cell Error Mode" value "TextValue" is a good choice for majority PowerPivot DAX users and in this setup you can see cell that cause error and you can see error message. But with this setting you should be aware of special case described above when Excel does not show cell that caused error.

A big thanks goes to Marius Dumitru and Jeffrey Wang from the Microsoft SSAS/PowerPivot team for helping me with this topic.