Excel PowerPivot Relationship Problem

[pbower]

Hi all,

I have a many-to-one relationship in Excel PowerPivot that was previously working properly. I have confirmed that the 'one' is definitely a 'one' (i.e. no duplicate values), and I have also made the relationship on a 'value(text)' formula for each, to ensure the data types in each are consistent. They are all very standard 7 digit numbers, i.e. 1382752, 1382753 etc.

Today, the relationship is broken - all calculated columns based on a related() function to the connected 'many' table are broken. I tried deleting and re-creating the relationship, and it came up with SQL dump and the following message:

============================
Error Message:
============================
An unexpected error occurred (file 'xmvsquery.cpp', line 1739, function 'XMVSColumn::Bind').
The process operation ended because the number of errors encountered during processing reached the defined limit of allowable errors for the operation.
The operation has been cancelled.
An unexpected error occurred (file 'xmvsquery.cpp', line 1739, function 'XMVSColumn::Bind').
The process operation ended because the number of errors encountered during processing reached the defined limit of allowable errors for the operation.
An unexpected error occurred (file 'xmvsquery.cpp', line 1739, function 'XMVSColumn::Bind').
The process operation ended because the number of errors encountered during processing reached the defined limit of allowable errors for the operation.
----------------------------
An unexpected error occurred (file 'xmvsquery.cpp', line 1739, function 'XMVSColumn::Bind').
The process operation ended because the number of errors encountered during processing reached the defined limit of allowable errors for the operation.
The operation has been cancelled.
An unexpected error occurred (file 'xmvsquery.cpp', line 1739, function 'XMVSColumn::Bind').
The process operation ended because the number of errors encountered during processing reached the defined limit of allowable errors for the operation.
An unexpected error occurred (file 'xmvsquery.cpp', line 1739, function 'XMVSColumn::Bind').
The process operation ended because the number of errors encountered during processing reached the defined limit of allowable errors for the operation.

============================
Call Stack:
============================
at Microsoft.AnalysisServices.Modeler.Storage.DataModelingSandbox.ExecuteCaptureLogAndProcessResults(OperationType type, Boolean cancellable, Boolean raw)
at Microsoft.AnalysisServices.Modeler.Storage.DataModelingSandbox.ExecuteAMOCode(OperationType type, OperationCancellability cancellable, AMOCode code)
at Microsoft.AnalysisServices.Modeler.Storage.SandboxTransaction.CommitInternal(Boolean finalCommit)
----------------------------
at Microsoft.AnalysisServices.Modeler.Storage.SandboxTransaction.CommitInternal(Boolean finalCommit)
at Microsoft.AnalysisServices.Modeler.Storage.SandboxTransaction.Commit()
at Microsoft.AnalysisServices.Modeler.Controls.RelationshipController.CreateRelationship(DataModelingColumn sourceColumn, DataModelingColumn relatedColumn)
at Microsoft.AnalysisServices.Modeler.Controls.RelationshipController.formCreateRelationshipDialog_Confirming(Object sender, EventArgs e)
at Microsoft.AnalysisServices.Modeler.Controls.CreateRelationshipDialog.buttonOK_Click(Object sender, EventArgs e)
============================



Does anybody have any idea at all what this means?

Thanks for your time and assistance.

Best regards,

Peter

 

[pbower]

Anyone?

Thanks

Pete

 

[pbower]

Please could anyone help me with this issue?

 

[pbower]

Pleaaase MR EXCEL

 

[pbower]

For anyone who is interested, SUMX and COUNTX etc. do not seem to work properly against calculated columns, in some instances, even when the data types for the relationship and everything else is fine!!!!!!!!!!!!!!!

THIS WAS DOING MY HEAD IN - half my columns with occurences in both tables would turn up blank, and then all would appear, and then all blank again, at NO FAULT OF MY OWN.

The solution is to always use =CALCULATE() with a FILTER(RELATIONSHIPTABLE2, RELATIONSHIPTABLE2VALUE=RELATIONSHIPTABLE1VALUE) attached.

BLOODY POWER PIVOT STEAL MY LIFE

If anyone wants me to actually post this in a way that makes sense when I haven't been up for 2 days trying to figure it out in time for a deadline, feel free to ask and I will post when composed.

I can't believe a product like this actually has effed up functions like SUMX that don't even work properly!!!!!!!!!

 

[Fazza]

hi, Peter. I know nothing about power pivot. Though I do know Excel, SQL, advanced pivot tables, ADO & VBA: likely what you want can be done another way. What about posting some sample data and explanation/s and see how we go? I'll be offline very soon (for my night). regards, Fazza