Answered by:
DAX error "Formula is invalid" when updating underlying data, but not changing the formula?
Question

Hello,
first of all: I use the 64bit versions of Excel 2010 and PowerPivot on Windows Server 2008 R2.
I use this formula to calculate the median of my data:
MINX( FILTER( VALUES( TableName[ColumnName] ),
CALCULATE( COUNTROWS( TableName ),
TableName[ColumnName] <= EARLIER( TableName[ColumnName] ) )
> COUNTROWS( TableName ) * 0.5 ),
TableName[ColumnName] )The data comes from a view on an MS SQL Server and has about 3.5 million rows. With one dataset ("dataset 1"), everything is working out fine and VERY fast ;). When I change the view on the SQL server to filter for different data ("dataset 2", the result set still containing about 3.5 million rows) and update the PowerPivot data and then the pivot table, the status bar reads "Executing OLAP query..." and excel.exe utilizes one CPU core at 100% for a long time and its memory usage increases significantly, but nothing happens. If I interrupt that process by pressing Esc, I get the following error (original German text included):
============================
Fehlermeldung: (Error message:)
============================
Ausnahme von HRESULT: 0x800A03EC (Exception from HRESULT: ...)


Das MedianFeld konnte der PivotTable nicht hinzugefügt werden, weil die Formel ungültig ist. (Could not add the field "Median" to the PivotTable because the formula is invalid.)
============================
Aufrufliste: (Stack trace:)
============================
Server stack trace:
Exception rethrown at [0]:
bei System.Runtime.Remoting.Proxies.RealProxy.HandleReturnMessage(IMessage reqMsg, IMessage retMsg)
bei System.Runtime.Remoting.Proxies.RealProxy.PrivateInvoke(MessageData& msgData, Int32 type)
bei Microsoft.Office.Interop.Excel.PivotTable.AddDataField(Object Field, Object Caption, Object Function)
bei Microsoft.AnalysisServices.Modeler.FieldList.ExcelInterOpUtil.AddToDataFields(ICalculatedMember calculatedMember, Int32 positionIndex, Boolean isSpecialColumnBasedNamedSetPresent)

bei Microsoft.AnalysisServices.Modeler.FieldList.ExcelInterOpUtil.AddToDataFields(ICalculatedMember calculatedMember, Int32 positionIndex, Boolean isSpecialColumnBasedNamedSetPresent)
bei Microsoft.AnalysisServices.Modeler.FieldList.GeminiPivot.SetFieldOrientation(FieldLocation location, IGeminiColumn column, Int32 positionIndex)

bei Microsoft.AnalysisServices.Modeler.FieldList.GeminiPivot.SetFieldOrientation(FieldLocation location, IGeminiColumn column, Int32 positionIndex)
bei Microsoft.AnalysisServices.Modeler.FieldList.GeminiPivot.AddField(IGeminiColumn column, Int32 index)
bei Microsoft.AnalysisServices.Modeler.FieldList.FieldListControl.fieldsTreeView_AfterCheck(Object sender, TreeViewEventArgs e)
============================I have analyzed the two datasets for differences and found the following ones:
 Dataset 2 has also negative values in its ColumnName column, dataset 1 does not. Filtering dataset 2 (by changing the view on the SQL server) so that the column contains only positive values does not help.
 A displayed column contains text with square brackets in it in dataset 2. Changing the SQL view to replace them with an empty string (replace(column2, '[', '')) does not help.
I do not know what else to try. Can anybody help me? The two datasets are very large, but if anyone can give a recommendation how to export them in a reasonable size, I can make them available.
Best regards
Michael
 Edited by Michael Kohnen Saturday, February 25, 2012 7:42 PM
Saturday, February 25, 2012 7:40 PM
Answers

Hi Michael
I am assuming you want to calculate the quartile values of the last column on each data set, regarless of the probablity or threshold values. Is this correct?
I describe a method for calculating quartiles in here http://javierguillen.wordpress.com/2011/09/13/quartilepercentileandmedianinpowerpivotdax/ , however it may not always be the best solution as it requires an extra AddIn and some understanding of MDX and the multidimensional interface of the PowerPivot tabular model.
I suggest looking at the great blog post by Colin Banfield in which he uses DAX exclusively for computing quartiles ( http://www.powerpivotpro.com/2011/09/creatingaccuratepercentilemeasuresindax%e2%80%93parti/ ) With this method, I computed the 25th quartile on your dataset # 2:
If you have any trouble following the step by step guide on that blog entry let me know and I will be happy to help.
Javier Guillen
http://javierguillen.wordpress.com/ Edited by Javier GuillenEditor Thursday, March 1, 2012 4:20 AM
 Marked as answer by Challen Fu Monday, March 5, 2012 10:09 AM
 Unmarked as answer by Michael Kohnen Monday, March 5, 2012 5:15 PM
 Marked as answer by Ed Price  MSFTMicrosoft employee Monday, October 28, 2013 7:23 AM
Thursday, March 1, 2012 4:18 AMAnswerer
All replies

Hi Michael,
To start troubleshooting, have you tried executing each part of the expression on its own? For example, take
CALCULATE( COUNTROWS( TableName ) )
and execute it as a calculated column on dataset 2. Once you do this successfully, try the countrows filtered by the EARLIER row context value, on its own:
CALCULATE( COUNTROWS( TableName ), TableName[ColumnName] <= EARLIER( TableName[ColumnName] ) )
does this come back successfully?
Javier Guillen
http://javierguillen.wordpress.com/Monday, February 27, 2012 4:31 AMAnswerer 
Dear Javier,
thanks for your reply. I have not tested the parts of the expressions on their own  I am new to PowerPivot and DAX and did not know where to start. I got the formula from your blog entry http://msbicentral.com/Resources/Articles/tabid/88/articleType/ArticleView/articleId/162/MediancalculationinPowerPivotDAX.aspx.
I have tested both of your expressions with dataset 1, which should succeed. Surprisingly, the second one does not work: When I enter it, I get the following error (German original included):
EARLIER/EARLIEST verweist auf einen früheren Zeilenkontext, der nicht vorhanden ist.
EARLIER/EARLIEST refers to an earlier row context which doesn’t exist.
I am wondering how the complete formula works  there seems to be missing some part now.
I need a formula to calculate quantiles (especially the 25%, 50% (median) and 75% quartiles). As it is your blog entry I got the formula from, do you have any hint?
Best regards
Michael
 Edited by Michael Kohnen Monday, February 27, 2012 11:12 AM
Monday, February 27, 2012 10:38 AM 
Hi Michael,
Are you trying to determine the quartile values as a DAX measure or as a calculated column? The approach may be different depending on what type of expression you are using. Also, can you post a small sample of what your data looks like?
Javier Guillen
http://javierguillen.wordpress.com/Monday, February 27, 2012 3:00 PMAnswerer 
Hello Javier,
I use a new measure (button "New Measure" in the toolbar or option in the context menu of the table). Here are the data samples:
Dataset 1:
Probability RThreshold SThreshold vector value
0 1 1 DHTTestApp: Total GET Success Ratio 0.98
0 1 1 DHTTestApp: Total GET Success Ratio 1
0 1 1 DHTTestApp: Total GET Success Ratio 1
0 1 1 DHTTestApp: Total GET Success Ratio 1
0 1 1 DHTTestApp: Total GET Success Ratio 1
0 1 1 DHTTestApp: Total GET Success Ratio 0.98
0 1 1 DHTTestApp: Total GET Success Ratio 1
0 1 1 DHTTestApp: Total GET Success Ratio 1
0 1 1 DHTTestApp: Total GET Success Ratio 1
0 1 1 DHTTestApp: Total GET Success Ratio 1Dataset 2:
Probability RThreshold SThreshold vector value
0 1 1 [MKTBR] BaseOverlay: All nodes: Own routing trust value 0.011353711790393
0 1 1 [MKTBR] BaseOverlay: All nodes: Own routing trust value 0.20522161505768
0 1 1 [MKTBR] BaseOverlay: All nodes: Own routing trust value 0.12309191295875
0 1 1 [MKTBR] BaseOverlay: All nodes: Own routing trust value 0.26926457661881
0 1 1 [MKTBR] BaseOverlay: All nodes: Own routing trust value 0.1911946574326
0 1 1 [MKTBR] BaseOverlay: All nodes: Own routing trust value 0.066699727250186
0 1 1 [MKTBR] BaseOverlay: All nodes: Own routing trust value 0.32597014925373
0 1 1 [MKTBR] BaseOverlay: All nodes: Own routing trust value 0.11977454203852
0 1 1 [MKTBR] BaseOverlay: All nodes: Own routing trust value 0.24751410911045
0 1 1 [MKTBR] BaseOverlay: All nodes: Own routing trust value 0.076218041485769In both datasets, probability has a range from 0 to 1, both thresholds from 1 to 1. In dataset 1, value has a range from 0 to 1, in dataset 2, it's 1 to 1.
Thanks for your help!
Tuesday, February 28, 2012 2:37 PM 
Hi Michael
I am assuming you want to calculate the quartile values of the last column on each data set, regarless of the probablity or threshold values. Is this correct?
I describe a method for calculating quartiles in here http://javierguillen.wordpress.com/2011/09/13/quartilepercentileandmedianinpowerpivotdax/ , however it may not always be the best solution as it requires an extra AddIn and some understanding of MDX and the multidimensional interface of the PowerPivot tabular model.
I suggest looking at the great blog post by Colin Banfield in which he uses DAX exclusively for computing quartiles ( http://www.powerpivotpro.com/2011/09/creatingaccuratepercentilemeasuresindax%e2%80%93parti/ ) With this method, I computed the 25th quartile on your dataset # 2:
If you have any trouble following the step by step guide on that blog entry let me know and I will be happy to help.
Javier Guillen
http://javierguillen.wordpress.com/ Edited by Javier GuillenEditor Thursday, March 1, 2012 4:20 AM
 Marked as answer by Challen Fu Monday, March 5, 2012 10:09 AM
 Unmarked as answer by Michael Kohnen Monday, March 5, 2012 5:15 PM
 Marked as answer by Ed Price  MSFTMicrosoft employee Monday, October 28, 2013 7:23 AM
Thursday, March 1, 2012 4:18 AMAnswerer 
Hi Javier,
no, I want to calculate the quartiles of the last column in dependence of the values in Probability, RThreshold and SThreshold. Probability is used as a report filter, one threshold in the columns of the pivot table, one in the rows. It should look like this:
For Probability = 0.05
1.0 0.9 ... 1.0 0.02375 0.34274 ... 0.9 0.7933 0.2846739 ... ... ... ... ... Best regards
Michael
Monday, March 5, 2012 5:23 PM 
Michael,
I tried but couldn't create a valid scenario for what you describe. The data you posted didn't appear to have a valid delimiter, and for the example you posted of probability 0.05 I dont find that probability value on the data on your message.
The blog post I mentioned on my last post can be used to deal with any scenario that needs to compute quartile values, regardless of your final pivot table structure.
Javier Guillen
http://javierguillen.wordpress.com/Tuesday, March 6, 2012 12:14 AMAnswerer 
Michael,
Is this still an issue?
Thank you!
Ed Price, SQL Server Customer Program Manager (Blog, Small Basic, Wiki Ninjas, Wiki)
Answer an interesting question? Create a wiki article about it!Monday, October 28, 2013 7:23 AM 
Hi Ed,
I could not solve the problem with PowerPivot, so I had to use another way to get my results. So, yes, I believe the problem still exists in general, but I do not need the solution any more.
Tuesday, October 29, 2013 9:31 PM 
It might even not be related to the formula anyway ...
I got the same error over and over again when I tried to add a second formula into my PowerPivot. Then during testing I reduced the second formula more and more towards the first formula until they became similar  and get the same result. Even when I copy the formula from the first expression into the second.
In both cases, the formula creation dialogue indicates that the formula is ok.
But when I try to add them to the PowerPivot, the first one is taken and shows correct results. The second  identical  one comes back with the error message and is not shown in the PivotTable afterwards, but is listed in the fieldlist.
Strange.
Thursday, February 12, 2015 11:26 AM