locked
Error in Power Pivot (Excel 2016) function XMVSXJoin::BuildRelationships ; when adding a calculated column using CALCULATE RRS feed

  • Question

  • Hi,
    I am following through some very simple examples of using CALCULATE, and when I add the following to my Products table I get the error show below.

    • I use excel 2016 pro 64bits version 1711 build 8730.2127
    • I have found the same kind of issue on an other forum (https://social.msdn.microsoft.com/Forums/sqlserver/en-US/8aa7d39f-a04c-4753-bc70-59b2c50cf33e/error-in-power-pivot-excel-2016-function-xmvsxjoinbuildrelationships-when-adding-a-calculated?forum=sqlkjpowerpivotforexcel) :
      => I have tried, but without success, to "reboot the computer. Then disable Power Pivot for Excel 2016, then enable it again".

    =CALCULATE(SUM(Sales[ExtendedAmount]))

    It gaves an Error Message:
    "   An unexpected error occurred (file 'xmvsquery.cpp', line 13960, function 'XMVSXJoin::BuildRelationships').
    The process operation ended because the number of errors encountered during processing reached the defined limit of allowable errors for the operation.
    The current operation was cancelled because another operation in the transaction failed.
    ----------------------------
    Call Stack:
    ============================
       at Microsoft.AnalysisServices.BackEnd.DataModelingSandbox.ProcessResults(XmlaResultCollection results)
       at Microsoft.AnalysisServices.BackEnd.DataModelingSandbox.ExecuteCaptureLogAndProcessResults(OperationType type, Boolean cancellable, Boolean raw, Boolean raiseEvents)
       at Microsoft.AnalysisServices.BackEnd.SandboxTransaction.CommitInternal(Boolean finalCommit)
    ----------------------------
       at Microsoft.AnalysisServices.BackEnd.SandboxTransaction.CommitInternal(Boolean finalCommit)
       at Microsoft.AnalysisServices.Common.SandboxEditor.ChangeFormula(TableWidgetPanel currentTable, IList`1 colIndices, IList`1 names, IList`1 expressions, Boolean doFormulaBarCommit, IList`1 displayIndices)
    ============================   "

    Any idee ?
    Thanks by advance.

    Tuesday, December 26, 2017 11:13 AM

Answers

  • Hi Gérard52,

    Thanks for your response.

    Besides Power Pivot, Excel and Office issues, the root cause for this issue might be lots of other things either. With this limited information, it is pretty hard to answer this question. For this issue, I would suggest opening a case with Microsoft Customer Support Services (CSS) (http://support.microsoft.com), so that a dedicated Support Professional can assist you in a more efficient manner. 

    Best Regards
    Willson Yuan
    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Tuesday, January 2, 2018 2:06 AM

All replies

  • Hi Gérard52,

    Thanks for your question.

    My Excel version is 2016 MSO(16.0.4591.1000)64-bit, I can not reproduce this issue as your said. See below images:

    Can you reproduce this issue in another computer with the same data ?

    One more thing, the issue may be caused by other excel or office issues, you'd better post it to office forum to get professional support. Here is the link https://social.technet.microsoft.com/Forums/office/en-US/home?category=officeitpro.


    Best Regards
    Willson Yuan
    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Wednesday, December 27, 2017 2:11 AM
  • Hi Willson,
    Thanks for your help.
    (I have to remove below links and screen shots as my account is not yet validated)

    • If it could helps, here is a link  to my excel file
      ( https://www.dropbox.com/s/qggzev6vxvd7hz2/Learn%20to%20write%20DAX%20-%20AdventureWorks.xlsx?dl=0 ).
      Nothing secret, It's the AdventureWorks data, and the exercice from the book Learn to Write DAX: A Practical Guide to Learning Power Pivot for Excel and Power BI

    • I'm not sure you're reproducing the same test as me. It seems you are "calculating" in the same table?
      I've been able to add such formula in my "Products" table and it works well :  =if(FIND("Mountain";Products[ProductName];1;0)>0;TRUE();FALSE())

    • My issue came when I've tried to add a new column in my "Products" table (dim table)  "=CALCULATE(SUM(Sales[ExtendedAmount]))".
      Sales[ExtendedAmount] is in my "Sales" table (facts table).

    • I have the same issue, on the same excel file, on 2 different computers ( 1 tower + 1 laptop)
      Same Excel version : Microsoft Excel 2016 MSO (16.0.8730.2046) 64 bits
      With 2 different official excel licenses
      Edit : the same issue with my professionnal laptop with excel 2013 (15.0.4953.1000 64 bits. No error message, but the added column remains empty.

    • My windows 10 is a 64 bit version as well, version 1709   16299.125

    Best regards

    • Edited by Gérard52 Wednesday, December 27, 2017 4:27 PM
    Wednesday, December 27, 2017 10:29 AM
  • Hi Gérard52,

    Thanks for your response.

    I have downloaded your Excel file, and tried to create a new column in "Products" table, but everything works fine, still can not reproduce this issue. See below image:


    Best Regards
    Willson Yuan
    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Thursday, December 28, 2017 5:53 AM
  • Hi Willson,
    Thanks for your help.

    You're more lucky than me.

    • I've tried with an other source of data : AdventureWorks DW 2012.accdb
        => same issue : "error...line 13960...'XMVSXJoin::BuildRelationships'"
    • I've tried "from scratch" with simple tables, linked to powerpivot datamodel
        => I've been able to have the right figures calculated in my little Products calculated column

    Remains incomprehensible for me.

    My searches on Internet tells me that some other people have met the same issue than me, without clear solution.

    Thanks again for your help.
    I will continue to read my books... without doing by my own the exercises involving calculated columns in the "dim" tables.

    Monday, January 1, 2018 10:52 AM
  • Hi Gérard52,

    Thanks for your response.

    Besides Power Pivot, Excel and Office issues, the root cause for this issue might be lots of other things either. With this limited information, it is pretty hard to answer this question. For this issue, I would suggest opening a case with Microsoft Customer Support Services (CSS) (http://support.microsoft.com), so that a dedicated Support Professional can assist you in a more efficient manner. 

    Best Regards
    Willson Yuan
    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Tuesday, January 2, 2018 2:06 AM
  • Hi, any update on this? (maybe some fix has been released...)

    I have the same issue but the excel version is

    Excel 2016 MSO(16.0.8431.2110) 32-bit.

    In case of news, please post a comment here..

    Thanks

    Maurizio

    Sunday, March 4, 2018 7:55 AM