Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
Hello,
When I tried to open a specific .pbix file, I got an "Unexpected Error". The message is: "Failed to save modifications to the server. Error returned: 'An argument of function 'DATE' has the wrong data type or the result is too large or too small.". I haven't send a Frown yet. There is private data on that file, so I am willing to do so.
Thanks for the support
is there a solution to this yet ? I have the same problem the entire last week I could work on my file no problem on Friday afternoon (04/06/2021) I saved and logged of for the weekend. When i got back to work on Monday (07/06/2021) there is no why I could access my pbix file. There where no errors in my pbix file and I don't understand how you should fix a problem in a file if you access the dam thing. There is another error with the data source but that isn't the problem. that error occours randomly since I started having this problem. and even when that part of the error is not present I get the exact same result :
Failed to save modifications to the server. Error returned: 'An argument of function 'DATE' has the wrong data type or the result is too large or too small.
An argument of function 'DATE' has the wrong data type or the result is too large or too small.
My Data source is a direct query on an Asure SQL db. which one again didn't have any issues up untill this error.
Here is my stack trace
Feedback Type:
Frown (Error)
Error Message:
Failed to save modifications to the server. Error returned: 'An argument of function 'DATE' has the wrong data type or the result is too large or too small.
An argument of function 'DATE' has the wrong data type or the result is too large or too small.
The current operation was cancelled because another operation in the transaction failed.
A connection could not be made to the data source with the Name of '{"protocol":"tds","address":{"server":"my server name,1433","database":"myDatabaseName"},"authentication":null,"query":null}'.
'.
Stack Trace:
Microsoft.AnalysisServices.OperationException
at Microsoft.AnalysisServices.Tabular.Model.SaveChangesImpl(SaveOptions options)
at Microsoft.PowerBI.Modeler.ModelManager.<SaveChanges>b__234_0()
at Microsoft.PowerBI.Client.Windows.Modeling.Hosting.ModelingTelemetryService.<>c__DisplayClass6_0`1.<RunInActivity>b__0()
at Microsoft.PowerBI.Client.Windows.Modeling.Hosting.ModelingTelemetryService.RunInActivity(ModelingActivityKind activity, Action action)
at Microsoft.PowerBI.Client.Windows.Modeling.Hosting.ModelingTelemetryService.RunInActivity[T](ModelingActivityKind activity, Func`1 action)
at Microsoft.PowerBI.Modeler.ModelManager.SaveChanges(String description)
at Microsoft.PowerBI.Modeler.ModelManager.InternalTransaction.Commit(Boolean recalc)
at Microsoft.PowerBI.Modeling.Engine.Authoring.AsyncModelAuthoringService.ModelAuthoringTransaction.Commit(RecalcBehavior recalcBehavior)
at Microsoft.PowerBI.Client.Windows.ReportPreparer.<>c.<PerformRecalc>b__21_0(IExtendedModelChangeScope scope)
at Microsoft.PowerBI.Modeling.Engine.Authoring.AsyncModelAuthoringService.RunOutOfBandExtendedModelChange(Action`1 extendedModelChange, IDataModel dataModel, IModelManager modelManager, Boolean roleChangesImpactQueries, IRefreshPolicyUpdater refreshPolicyUpdater, Boolean skipDataModelTracing)
at Microsoft.PowerBI.Modeling.Engine.ModelingEngine.RunOutOfBandExtendedModelChange(Action`1 extendedModelChange, IDataModel dataModel, IModelManager modelManager, Boolean roleChangesImpactQueries, IRefreshPolicyUpdater refreshPolicyUpdater)
at Microsoft.PowerBI.Client.Windows.AnalysisServices.AnalysisServicesDatabaseLocal.RunOutOfBandExtendedModelChange(Action`1 extendedModelChange, IRefreshPolicyUpdater refreshPolicyUpdater, Boolean requireASDBToBeUnattached)
at Microsoft.PowerBI.Client.Windows.ReportPreparer.PerformRecalc(Report report, IPowerBIWindowService windowService)
at Microsoft.PowerBI.Client.Windows.ReportPreparer.Prepare(Report report, PackageValidationResult mashupResult, IPowerBIWindowService windowService, String fileName, ILuciaFeatureSwitchProxy luciaFeatureSwitchProxy, Nullable`1 entryPoint, Version pbixFileVersion, Boolean suppressRecalc)
at Microsoft.PowerBI.Client.Windows.Services.PowerBIPackagingService.Open(Stream stream, String streamName, IPowerBIWindowService windowService, Nullable`1 entryPoint, PowerBIPackageOpenOptions options, ReportPreparerResult& prepareResult)
at Microsoft.PowerBI.Client.Windows.Services.FileManager.<LoadFromPbix>d__23.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at Microsoft.PowerBI.Client.Windows.Services.UIBlockingService.<>c__DisplayClass14_0`1.<<BlockUIAndRun>b__0>d.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at Microsoft.PowerBI.Client.Windows.Services.UIBlockingService.WaitOnUIThreadForTaskCompletion[T](Task`1 task)
at Microsoft.PowerBI.Client.Windows.Services.UIBlockingService.BlockUIAndRun[T](Func`1 asyncMethod)
at Microsoft.PowerBI.Client.Windows.Services.FileManager.OpenFile(IPowerBIWindowService windowService, IPbixFile fileToOpen, Nullable`1 entryPoint)
at Microsoft.PowerBI.Client.Program.TryOpenOrCreateReport(IUIHost uiHost, IPowerBIWindowService windowService, IFileManager fileManager, IFileHistoryManager fileHistoryManager, String filePath, FileType fileType, IFileInformationProtectionManager fileInformationProtectionManager)
at Microsoft.PowerBI.Client.Program.<>c__DisplayClass4_0.<Main>b__1()
at Microsoft.PowerBI.Client.Windows.IExceptionHandlerExtensions.<>c__DisplayClass3_0.<HandleExceptionsWithNestedTasks>b__0()
at Microsoft.Mashup.Host.Document.ExceptionHandlerExtensions.HandleExceptions(IExceptionHandler exceptionHandler, Action action)
Stack Trace Message:
Failed to save modifications to the server. Error returned: 'An argument of function 'DATE' has the wrong data type or the result is too large or too small.
An argument of function 'DATE' has the wrong data type or the result is too large or too small.
The current operation was cancelled because another operation in the transaction failed.
A connection could not be made to the data source with the Name of '{"protocol":"tds","address":{"server":"my server name","database":"myDatabaseName"},"authentication":null,"query":null}'.
'.
Invocation Stack Trace:
at Microsoft.Mashup.Host.Document.ExceptionExtensions.GetCurrentInvocationStackTrace()
at Microsoft.Mashup.Client.UI.Shared.StackTraceInfo..ctor(String exceptionStackTrace, String invocationStackTrace, String exceptionMessage)
at Microsoft.PowerBI.Client.Windows.Telemetry.PowerBIUserFeedbackServices.GetStackTraceInfo(Exception e)
at Microsoft.PowerBI.Client.Windows.Telemetry.PowerBIUserFeedbackServices.ReportException(IWindowHandle activeWindow, IUIHost uiHost, FeedbackPackageInfo feedbackPackageInfo, Exception e, Boolean useGDICapture)
at Microsoft.Mashup.Client.UI.Shared.UnexpectedExceptionHandler.<>c__DisplayClass15_0.<HandleException>b__0()
at Microsoft.Mashup.Client.UI.Shared.UnexpectedExceptionHandler.HandleException(Exception e)
at Microsoft.Mashup.Host.Document.ExceptionHandlerExtensions.HandleExceptions(IExceptionHandler exceptionHandler, Action action)
at Microsoft.PowerBI.Client.Program.Main(String[] args)
PowerBINonFatalError:
{"AppName":"PBIDesktop","AppVersion":"2.93.981.0","ModuleName":"Microsoft.PowerBI.Tabular.dll","Component":"Microsoft.AnalysisServices.Tabular.Model","Error":"Microsoft.AnalysisServices.OperationException","MethodDef":"SaveChangesImpl - PFE_XL_EXCEL_NUM,PFE_SERVERBASE_OPERATION_CANCELLED_BECAUSE_OTHER_ONE_FAILED,PFE_HRE_CONNECTION_FAILED_NO_ID","ErrorOffset":"415"}
PowerBINonFatalError_MethodDefDescription:
PFE_XL_EXCEL_NUM,PFE_SERVERBASE_OPERATION_CANCELLED_BECAUSE_OTHER_ONE_FAILED,PFE_HRE_CONNECTION_FAILED_NO_ID
ModelChangeResult:
{"changes":[]}
PowerBIUserFeedbackServices_IsReported:
True
Has anyone find a solution to this? It is the 4th month that this happens at the begining of the month. I cannot open the .pbix file and the refreshes sarted failing. Everything was fine 2 days ago and everything goes back to normal 2 days after. This is so anoying as the information at the begining of the month is so sensitive for my management team.
They are loosing trust on this tool... please help!
Here are the errors in case someone is experiencing the same...
Data refresh error:
An argument of function 'DATE' has the wrong data type or the result is too large or too small. The current operation was cancelled because another operation in the transaction failed.
.Pbix file error when trying to open the file:
Feedback Type:
Frown (Error)
Error Message:
Failed to save modifications to the server. Error returned: 'An argument of function 'DATE' has the wrong data type or the result is too large or too small.
The current operation was cancelled because another operation in the transaction failed.
'.
Stack Trace:
at Microsoft.AnalysisServices.Tabular.Model.SaveChangesImpl(SaveOptions options)
at Microsoft.PowerBI.Modeler.ModelManager.<SaveChanges>b__230_0()
at Microsoft.PowerBI.Client.Windows.Modeling.Hosting.ModelingTelemetryService.<>c__DisplayClass6_0`1.<RunInActivity>b__0()
at Microsoft.PowerBI.Client.Windows.Modeling.Hosting.ModelingTelemetryService.RunInActivity(ModelingActivityKind activity, Action action)
at Microsoft.PowerBI.Client.Windows.Modeling.Hosting.ModelingTelemetryService.RunInActivity[T](ModelingActivityKind activity, Func`1 action)
at Microsoft.PowerBI.Modeler.ModelManager.SaveChanges(String description)
at Microsoft.PowerBI.Modeler.ModelManager.InternalTransaction.Commit(Boolean recalc)
at Microsoft.PowerBI.Modeling.Engine.Authoring.AsyncModelAuthoringService.ModelAuthoringTransaction.<>c__DisplayClass17_0.<Commit>b__0()
at Microsoft.PowerBI.Modeling.Engine.Authoring.ModelAuthoringExceptionHandler.AddModelChangeResultToExceptions(Action action, ModelChangeResult modelChangeResult)
at Microsoft.PowerBI.Modeling.Engine.Authoring.AsyncModelAuthoringService.ModelAuthoringTransaction.Commit(RecalcBehavior recalcBehavior)
at Microsoft.PowerBI.Client.Windows.ReportPreparer.<>c.<PerformRecalc>b__22_0(IExtendedModelChangeScope scope)
at Microsoft.PowerBI.Modeling.Engine.Authoring.AsyncModelAuthoringService.RunOutOfBandExtendedModelChange(Action`1 extendedModelChange, IDataModel dataModel, IModelManager modelManager, Boolean roleChangesImpactQueries, IRefreshPolicyUpdater refreshPolicyUpdater, Boolean skipDataModelTracing)
at Microsoft.PowerBI.Modeling.Engine.ModelingEngine.RunOutOfBandExtendedModelChange(Action`1 extendedModelChange, IDataModel dataModel, IModelManager modelManager, Boolean roleChangesImpactQueries, IRefreshPolicyUpdater refreshPolicyUpdater)
at Microsoft.PowerBI.Client.Windows.AnalysisServices.AnalysisServicesDatabaseLocal.RunOutOfBandExtendedModelChange(Action`1 extendedModelChange, IRefreshPolicyUpdater refreshPolicyUpdater, Boolean requireASDBToBeUnattached)
at Microsoft.PowerBI.Client.Windows.ReportPreparer.PerformRecalc(Report report, IPowerBIWindowService windowService)
at Microsoft.PowerBI.Client.Windows.ReportPreparer.Prepare(Report report, PackageValidationResult mashupResult, IPowerBIWindowService windowService, String fileName, ILuciaFeatureSwitchProxy luciaFeatureSwitchProxy, Nullable`1 entryPoint, Version pbixFileVersion, Boolean suppressRecalc)
at Microsoft.PowerBI.Client.Windows.Services.PowerBIPackagingService.Open(Stream stream, String streamName, IPowerBIWindowService windowService, Nullable`1 entryPoint, PowerBIPackageOpenOptions options, ReportPreparerResult& prepareResult)
at Microsoft.PowerBI.Client.Windows.Services.FileManager.<LoadFromPbix>d__24.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at Microsoft.PowerBI.Client.Windows.Services.UIBlockingService.<>c__DisplayClass15_0`1.<<BlockUIAndRun>b__0>d.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at Microsoft.PowerBI.Client.Windows.Services.UIBlockingService.WaitOnUIThreadForTaskCompletion[T](Task`1 task)
at Microsoft.PowerBI.Client.Windows.Services.UIBlockingService.BlockUIAndRun[T](Func`1 asyncMethod)
at Microsoft.PowerBI.Client.Windows.Services.FileManager.OpenFile(IPowerBIWindowService windowService, IPbixFile fileToOpen, Nullable`1 entryPoint)
at Microsoft.PowerBI.Client.Program.TryOpenOrCreateReport(IUIHost uiHost, IPowerBIWindowService windowService, IFileManager fileManager, IFileHistoryManager fileHistoryManager, String filePath, FileType fileType)
at Microsoft.PowerBI.Client.Program.<>c__DisplayClass4_0.<Main>b__1()
at Microsoft.PowerBI.Client.Windows.IExceptionHandlerExtensions.<>c__DisplayClass3_0.<HandleExceptionsWithNestedTasks>b__0()
at Microsoft.Mashup.Host.Document.ExceptionHandlerExtensions.HandleExceptions(IExceptionHandler exceptionHandler, Action action)
Stack Trace Message:
Failed to save modifications to the server. Error returned: 'An argument of function 'DATE' has the wrong data type or the result is too large or too small.
The current operation was cancelled because another operation in the transaction failed.
'.
Invocation Stack Trace:
at Microsoft.Mashup.Host.Document.ExceptionExtensions.GetCurrentInvocationStackTrace()
at Microsoft.Mashup.Client.UI.Shared.StackTraceInfo..ctor(String exceptionStackTrace, String invocationStackTrace, String exceptionMessage)
at Microsoft.PowerBI.Client.Windows.Telemetry.PowerBIUserFeedbackServices.GetStackTraceInfo(Exception e)
at Microsoft.PowerBI.Client.Windows.Telemetry.PowerBIUserFeedbackServices.ReportException(IWindowHandle activeWindow, IUIHost uiHost, FeedbackPackageInfo feedbackPackageInfo, Exception e, Boolean useGDICapture)
at Microsoft.Mashup.Client.UI.Shared.UnexpectedExceptionHandler.<>c__DisplayClass15_0.<HandleException>b__0()
at Microsoft.Mashup.Client.UI.Shared.UnexpectedExceptionHandler.HandleException(Exception e)
at Microsoft.Mashup.Host.Document.ExceptionHandlerExtensions.HandleExceptions(IExceptionHandler exceptionHandler, Action action)
at Microsoft.PowerBI.Client.Program.Main(String[] args)
PowerBINonFatalError:
{"AppName":"PBIDesktop","AppVersion":"2.88.1385.0","ModuleName":"Microsoft.PowerBI.Tabular.dll","Component":"Microsoft.AnalysisServices.Tabular.Model","Error":"Microsoft.AnalysisServices.OperationException","MethodDef":"SaveChangesImpl - PFE_XL_EXCEL_NUM,PFE_SERVERBASE_OPERATION_CANCELLED_BECAUSE_OTHER_ONE_FAILED","ErrorOffset":"0"}
PowerBINonFatalError_MethodDefDescription:
PFE_XL_EXCEL_NUM,PFE_SERVERBASE_OPERATION_CANCELLED_BECAUSE_OTHER_ONE_FAILED
ModelChangeResult:
{"changes":[],"hasQueryImpact":false}
PowerBIUserFeedbackServices_IsReported:
True
Hello,
I'm having the same issue with a file that was posted two weeks ago, and everything was working correctly until today that a refesh failed and now i cannot open the file to try to find the solution.
Please help! not sure what to else to do
I enabled tracing. Here is the line related to the error:
DataMashup.Trace Error: 24579 : {"Start":"2016-11-30T15:18:55.7521748Z","Action":"UnexpectedExceptionHandler/HandleException","Exception":"Exception:\r\nExceptionType: Microsoft.AnalysisServices.OperationException, Microsoft.PowerBI.Amo.Core, Version=13.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91\r\nMessage: Failed to save modifications to the server. Error returned: 'An argument of function 'DATE' has the wrong data type or the result is too large or too small.\r\n'.\r\nStackTrace:\n\r\nServer stack trace: \r\n at Microsoft.AnalysisServices.Tabular.Model.SaveChanges(SaveOptions saveOptions)\r\n at Microsoft.AnalysisServices.Tabular.Model.SaveChanges(SaveFlags saveFlags)\r\n at Microsoft.PowerBI.Modeler.ModelManager.InternalTransaction.Commit()\r\n at Microsoft.PowerBI.Client.Windows.Modeling.AsyncModelAuthoringService.ModelAuthoringTransaction.<Commit>b__15()\r\n at Microsoft.PowerBI.Client.Windows.Modeling.ModelAuthoringExceptionHandler.AddModelChangeResultToExceptions(Action action, ModelChangeResult modelChangeResult)\r\n at Microsoft.PowerBI.Client.Windows.Modeling.AsyncModelAuthoringService.ModelAuthoringTransaction.Commit()\r\n at Microsoft.PowerBI.Client.Windows.ReportUpgrader.Upgrade(Report report)\r\n at Microsoft.PowerBI.Client.Windows.Report.Upgrade()\r\n at Microsoft.PowerBI.Client.Windows.Services.FileManager.OpenFile(String filePathToOpen, IWin32Window ownerWindow, ReportUpgradeResult& upgradeResult)\r\n at Microsoft.PowerBI.Client.Windows.Services.FileOperationUIHandler.<>c__DisplayClass55.<OpenLocalFileInternal>b__53()\r\n at Microsoft.PowerBI.Client.Windows.Services.UIBlockingService.<>c__DisplayClass1.<BlockUIAndRunAsync>b__0()\r\n at System.Threading.Tasks.Task.InnerInvoke()\r\n at System.Threading.Tasks.Task.Execute()\r\n\r\nException rethrown at [0]: \r\n at Microsoft.PowerBI.Client.Windows.Services.UIBlockingService.WaitOnUIThreadForTaskCompletion(Task task)\r\n at Microsoft.PowerBI.Client.Windows.Services.UIBlockingService.BlockUIAndRun(Action action)\r\n at Microsoft.PowerBI.Client.Windows.Services.FileOperationUIHandler.OpenLocalFileInternal(IWin32Window ownerWindow, WindowsHost windowsHost, ExplorationClientHost reportingClientHost, TelemetryUserActionId entryPoint, String filePath, Boolean fromRecentFile)\r\n at Microsoft.PowerBI.Client.Windows.Services.FileOperationUIHandler.OpenLocalFile(IWin32Window ownerWindow, WindowsHost windowsHost, ExplorationClientHost reportingClientHost, TelemetryUserActionId entryPoint, String filePath, Boolean fromRecentFile)\r\n at Microsoft.PowerBI.Client.Windows.Ribbon.EditorRibbonCommandActionHandler.OpenRecentFile(UInt32 itemIndex, String itemLabel)\r\n at Microsoft.PowerBI.Client.Windows.Ribbon.EditorRibbonCommandActionHandler.<CreateCommands>b__c(UInt32 commandId, UInt32 index, String label)\r\n at Microsoft.Mashup.Client.Windows.Ribbon.RibbonCommandHandler.Execute(UInt32 commandId, CommandExecutionVerb verb, PropertyKeyRef keyRef, PropVariantRef currentValue, IUISimplePropertySet commandExecutionProperties)\r\n\r\nDataItems\r\nData\r\nKey: ModelChangeResult\r\nValue: {\"changes\":[{\"upgradeFormatting\":{\"entity\":\"DateTableTemplate_972d455f-6ecd-4e57-8648-01696bca9a27\",\"property\":\"RowNumber-2662979B-1795-4F74-8F37-6A1BA8059B61\"}},{\"upgradeFormatting\":{\"entity\":\"DateTableTemplate_972d455f-6ecd-4e57-8648-01696bca9a27\",\"property\":\"Date\"}},{\"upgradeFormatting\":{\"entity\":\"DateTableTemplate_972d455f-6ecd-4e57-8648-01696bca9a27\",\"property\":\"Year\"}},{\"upgradeFormatting\":{\"entity\":\"DateTableTemplate_972d455f-6ecd-4e57-8648-01696bca9a27\",\"property\":\"MonthNo\"}},{\"upgradeFormatting\":{\"entity\":\"DateTableTemplate_972d455f-6ecd-4e57-8648-01696bca9a27\",\"property\":\"Month\"}},{\"upgradeFormatting\":{\"entity\":\"DateTableTemplate_972d455f-6ecd-4e57-8648-01696bca9a27\",\"property\":\"QuarterNo\"}},{\"upgradeFormatting\":{\"entity\":\"DateTableTemplate_972d455f-6ecd-4e57-8648-01696bca9a27\",\"property\":\"Quarter\"}},{\"upgradeFormatting\":{\"entity\":\"DateTableTemplate_972d455f-6ecd-4e57-8648-01696bca9a27\",\"property\":\"Day\"}},{\"upgradeFormatting\":{\"entity\":\"Entries\",\"property\":\"RowNumber-2662979B-1795-4F74-8F37-6A1BA8059B61\"}},{\"upgradeFormatting\":{\"entity\":\"Entries\",\"property\":\"Id\"}},{\"upgradeFormatting\":{\"entity\":\"Entries\",\"property\":\"ProjectId\"}},{\"u","ProductVersion":"2.40.4554.463 (PBIDesktop)","ActivityId":"00000000-0000-0000-0000-000000000000","Process":"PBIDesktop","Pid":82624,"Tid":1,"Duration":"00:00:00.0024758"}
Hey,
Can u please do one thing download new version of Power BI and lets try.
I thought I was running the latest version. Strange because I have the update notifications enabled. Maybe I missed the notification. I just upgraded to november, 2016 version (2.41.4581.361).
Unfortunately, I got the same error when opening the file. Power BI Desktop closes after I hit "Close" on the "Unexpected Error" message box.
I remember the modification I did yesterday that is probably the cause of the problem. In the query, I have 2 custom columns based on a date column that come from the data source. One column is for the month no, the other is for the month name. On the report, I am ordering the month name using the month no column. However, yesterday I deleted the month no by mistake. It's the last change I can remember that may have broken the report file.
Hi @Jonathan_P,
I remember the modification I did yesterday that is probably the cause of the problem. In the query, I have 2 custom columns based on a date column that come from the data source. One column is for the month no, the other is for the month name. On the report, I am ordering the month name using the month no column. However, yesterday I deleted the month no by mistake. It's the last change I can remember that may have broken the report file.
In order to reproduce this issue, I have done the same as mentioned above. However, I was not able to reproduce it on my side.
In this scenario, I would suggest you to sent the frown for better assistance on this issue.
Regards
As an update on this, the problem was in my DAX formulas. I made an error. But it was sad that I wasn't able to reopen the file. Fortunately, I did a backup before. Thanks for having replied.
Hi @v-ljerr-msft,
Thank you for taking the time to try to reproduce it. Like I said, this is the last thing I remember but maybe the cause is another modification.
I will look to probably send the frown about this issue. But since there is some private data in it I need the authorization before.
Thank you