none
DAX error "Formula is invalid" when updating underlying data, but not changing the formula?

    Question

  • Hello,

    first of all: I use the 64-bit 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 Median-Feld 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

    Saturday, February 25, 2012 7:40 PM

Answers

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 AM
    Answerer
  • 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/Median-calculation-in-PowerPivot-DAX.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

    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 PM
    Answerer
  • 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    1

    Dataset 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.076218041485769

    In 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/quartile-percentile-and-median-in-powerpivot-dax/ , however it may not always be the best solution as it requires an extra Add-In 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/creating-accurate-percentile-measures-in-dax-%e2%80%93-part-i/ )  With this method, I computed the 25th quartile on your dataset # 2:

    Image and video hosting by TinyPic

    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/


    Thursday, March 01, 2012 4:18 AM
    Answerer
  • 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 05, 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 06, 2012 12:14 AM
    Answerer
  • 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
    Owner
  • 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