BUG in Date functions in latest version (oct 2015) of Power BI Desktop

[MelissaSnell]

I installed Version: 2.27.4163.701 of PowerBI Desktop today.

I had a number of reports that use date-related filters in queries e.g. give me all the records where the date is in the last x days.

This used to work fine so I wish I hadn't upgraded.

I work using UK format dates e.g. dd/mm/yyyy

Now on every report and query that uses this kind of filter, I get the following error:

Error Message:

Unable to cast object of type 'SerializableFunctionValue' to type 'Microsoft.Mashup.Engine1.Library.Common.QueryResultFunctionValue'.

Stack Trace:

Microsoft.Mashup.Evaluator.Interface.ErrorException: Unable to cast object of type 'SerializableFunctionValue' to type 'Microsoft.Mashup.Engine1.Library.Common.QueryResultFunctionValue'. ---> Microsoft.Mashup.Evaluator.Interface.ErrorException: Unable to cast object of type 'SerializableFunctionValue' to type 'Microsoft.Mashup.Engine1.Library.Common.QueryResultFunctionValue'. ---> System.InvalidCastException: Unable to cast object of type 'SerializableFunctionValue' to type 'Microsoft.Mashup.Engine1.Library.Common.QueryResultFunctionValue'.
   at Microsoft.Mashup.Engine1.Library.Common.Creators.DbAstCreator.VisitInvocation(IInvocationExpression invocation)
   at Microsoft.Mashup.Engine1.Language.Ast.AstVisitor.VisitExpression(IExpression expression)
   at Microsoft.Mashup.Engine1.Language.Ast.AstVisitor.Visit(Object node)
   at Microsoft.Mashup.Engine1.Library.Common.Creators.DbAstCreator.CreateLogicalOperation(BinaryLogicalOperator predicate, IBinaryExpression binary)
   at Microsoft.Mashup.Engine1.Library.Common.Creators.DbAstCreator.VisitBinary(IBinaryExpression binary)
   at Microsoft.Mashup.Engine1.Language.Ast.AstVisitor.VisitExpression(IExpression expression)
   at Microsoft.Mashup.Engine1.Language.Ast.AstVisitor.Visit(Object node)
   at Microsoft.Mashup.Engine1.Library.Common.Creators.DbAstCreator.CreateConditionExpression(IExpression expression)
   at Microsoft.Mashup.Engine1.Library.Common.Creators.DbAstCreator.VisitBinary(IBinaryExpression binary)
   at Microsoft.Mashup.Engine1.Language.Ast.AstVisitor.VisitExpression(IExpression expression)
   at Microsoft.Mashup.Engine1.Library.Common.Creators.DbAstCreator.GetValue(IExpression node, TypeValue type)
   at Microsoft.Mashup.Engine1.Library.Common.Creators.DbAstCreator.GetValue(IExpression node)
   at Microsoft.Mashup.Engine1.Library.Common.Creators.DbAstCreator.VisitInvocation(IInvocationExpression invocation)
   at Microsoft.Mashup.Engine1.Language.Ast.AstVisitor.VisitExpression(IExpression expression)
   at Microsoft.Mashup.Engine1.Library.Common.Creators.DbAstCreator.GetValue(IExpression node, TypeValue type)
   at Microsoft.Mashup.Engine1.Library.Common.Creators.DbAstCreator.GetValue(IExpression node)
   at Microsoft.Mashup.Engine1.Library.Common.Creators.DbAstCreator.VisitInvocation(IInvocationExpression invocation)
   at Microsoft.Mashup.Engine1.Language.Ast.AstVisitor.VisitExpression(IExpression expression)
   at Microsoft.Mashup.Engine1.Library.Common.Creators.DbAstCreator.GetValue(IExpression node, TypeValue type)
   at Microsoft.Mashup.Engine1.Library.Common.Creators.DbAstCreator.CreateListSelect(IInvocationExpression invocation)
   at Microsoft.Mashup.Engine1.Library.Common.Creators.DbAstCreator.VisitInvocation(IInvocationExpression invocation)
   at Microsoft.Mashup.Engine1.Language.Ast.AstVisitor.VisitExpression(IExpression expression)
   at Microsoft.Mashup.Engine1.Library.Common.Creators.DbAstCreator.GetValue(IExpression node, TypeValue type)
   at Microsoft.Mashup.Engine1.Library.Common.Creators.DbAstCreator.GetValue(IExpression node)
   at Microsoft.Mashup.Engine1.Library.Common.DbEnvironment.Compile(Query originalQuery, IExpression expression, LogicalAstToCachedTypeflowResultCursor cursor)
   at Microsoft.Mashup.Engine1.Library.Common.QueryResultTableValue.get_Type()
   at Microsoft.Mashup.Engine1.Runtime.TableValue.get_Columns()
   at Microsoft.Mashup.Engine1.Language.Query.TableQuery.SelectColumns(ColumnSelection columnSelection)
   at Microsoft.Mashup.Engine1.Language.Query.QueryFolder.ExpressionTableValue.SelectColumns(ProjectColumnsQuery query)
   at Microsoft.Mashup.Engine1.Language.Query.QueryFolder.VisitSelectColumns(ProjectColumnsQuery query)
   at Microsoft.Mashup.Engine1.Language.Query.QueryFolder.VisitGroup(GroupQuery query)
   at Microsoft.Mashup.Engine1.Language.Query.QueryFolder.VisitAddColumns(AddColumnsQuery query)
   at Microsoft.Mashup.Engine1.Language.Query.QueryFolder.VisitAddColumns(AddColumnsQuery query)
   at Microsoft.Mashup.Engine1.Language.Query.QueryFolder.VisitSelectColumns(ProjectColumnsQuery query)
   at Microsoft.Mashup.Engine1.Language.Query.QueryFolder.VisitSkipTake(SkipTakeQuery query)
   at Microsoft.Mashup.Engine1.Language.Query.QueryFolder.VisitCombine(CombineQuery query)
   at Microsoft.Mashup.Engine1.Language.Query.QueryFolder.VisitSkipTake(SkipTakeQuery query)
   at Microsoft.Mashup.Engine1.Language.Query.QueryFolder.VisitCombine(CombineQuery query)
   at Microsoft.Mashup.Engine1.Language.Query.QueryFolder.VisitSkipTake(SkipTakeQuery query)
   at Microsoft.Mashup.Engine1.Language.Query.QueryFolder.VisitSelectColumns(ProjectColumnsQuery query)
   at Microsoft.Mashup.Engine1.Language.Query.QueryFolder.Fold(Query query)
   at Microsoft.Mashup.Engine1.Language.Query.QueryTableValue.GetEnumerator()
   at Microsoft.Mashup.Engine1.Runtime.TableValue.Microsoft.Mashup.Engine.Interface.ITableValue.GetEnumerator()
   at Microsoft.Mashup.Evaluator.SimpleDocumentEvaluator.ValuePreviewValueSource.TableValuePreviewValueSource.SerializeRows(Int32 count)
   at Microsoft.Mashup.Evaluator.SimpleDocumentEvaluator.ValuePreviewValueSource.TableValuePreviewValueSource.get_SmallValue()
   at Microsoft.Mashup.Evaluator.RemotePreviewValueSource.<>c__DisplayClass5.<RunStub>b__3()
   at Microsoft.Mashup.Evaluator.EvaluationHost.ReportExceptions(IHostTrace trace, IEngineHost engineHost, IMessageChannel channel, Action action)
   --- End of inner exception stack trace ---
   at Microsoft.Mashup.Common.SafeExceptions.IgnoreSafeExceptions(IHostTrace trace, Action action)
   at Microsoft.Mashup.Evaluator.EvaluationHost.TryReportException(IHostTrace trace, IEngineHost engineHost, IMessageChannel channel, Exception exception)
   at Microsoft.Mashup.Evaluator.EvaluationHost.ReportExceptions(IHostTrace trace, IEngineHost engineHost, IMessageChannel channel, Action action)
   at Microsoft.Mashup.Evaluator.RemotePreviewValueSource.RunStub(IEngineHost engineHost, IMessageChannel channel, Func`1 getPreviewValueSource)
   at Microsoft.Mashup.Evaluator.RemoteDocumentEvaluator.Service.<>c__DisplayClass32`1.<OnBeginGetResult>b__2f()
   at Microsoft.Mashup.Evaluator.EvaluationHost.ReportExceptions(IHostTrace trace, IEngineHost engineHost, IMessageChannel channel, Action action)
   at Microsoft.Mashup.Evaluator.RemoteDocumentEvaluator.Service.OnBeginGetResult[T](IMessageChannel channel, BeginGetResultMessage message, Action`1 action)
   at Microsoft.Mashup.Evaluator.RemoteDocumentEvaluator.Service.OnBeginGetPreviewValueSource(IMessageChannel channel, BeginGetPreviewValueSourceMessage message)
   at Microsoft.Mashup.Evaluator.MessageHandlers.TryDispatch(IMessageChannel channel, Message message)
   at Microsoft.Mashup.Evaluator.ChannelMessenger.ChannelMessageHandlers.TryDispatch(IMessageChannel channel, Message message)
   at Microsoft.Mashup.Evaluator.ChannelMessenger.OnMessageWithUnknownChannel(IMessageChannel baseChannel, MessageWithUnknownChannel messageWithUnknownChannel)
   at Microsoft.Mashup.Evaluator.MessageHandlers.TryDispatch(IMessageChannel channel, Message message)
   at Microsoft.Mashup.Evaluator.ChannelMessenger.ChannelMessageHandlers.TryDispatch(IMessageChannel channel, Message message)
   at Microsoft.Mashup.Evaluator.MessageHandlers.Dispatch(IMessageChannel channel, Message message)
   at Microsoft.Mashup.Evaluator.EvaluationHost.Run()
   at Microsoft.Mashup.Container.EvaluationContainerMain.Run(Object args)
   at Microsoft.Mashup.Evaluator.SafeThread2.<>c__DisplayClass15.<CreateAction>b__14(Object o)
   at Microsoft.Mashup.Container.EvaluationContainerMain.SafeRun(String[] args)
   at Microsoft.Mashup.Container.EvaluationContainerMain.Main(String[] args)
   --- End of inner exception stack trace ---
   at Microsoft.Mashup.Evaluator.EvaluationHost.OnException(IEngineHost engineHost, IMessageChannel channel, ExceptionMessage message)
   at Microsoft.Mashup.Evaluator.MessageHandlers.TryDispatch(IMessageChannel channel, Message message)
   at Microsoft.Mashup.Evaluator.MessageHandlers.Dispatch(IMessageChannel channel, Message message)
   at Microsoft.Mashup.Evaluator.ChannelMessenger.ChannelMessageHandlers.TryDispatch(IMessageChannel channel, Message message)
   at Microsoft.Mashup.Evaluator.MessageHandlers.Dispatch(IMessageChannel channel, Message message)
   at Microsoft.Mashup.Evaluator.Interface.IMessageChannelExtensions.WaitFor[T](IMessageChannel channel)
   at Microsoft.Mashup.Evaluator.RemotePreviewValueSource.PreviewValueSource.WaitFor(Func`1 condition, Boolean disposing)
   at Microsoft.Mashup.Evaluator.RemotePreviewValueSource.PreviewValueSource.get_SmallValue()
   at Microsoft.Mashup.Host.Document.Analysis.PackageDocumentAnalysisInfo.PackagePartitionAnalysisInfo.SetPreviewValue(EvaluationResult2`1 result, Func`1 getStaleSince, Func`1 getSampled)

Invocation Stack Trace:

   at Microsoft.Mashup.Host.Document.ExceptionExtensions.GetCurrentInvocationStackTrace()
   at Microsoft.Mashup.Client.ClientShared.StackTraceInfo..ctor(String exceptionStackTrace, String invocationStackTrace)
   at Microsoft.Mashup.Client.ClientShared.UnexpectedExceptionHandler.<>c__DisplayClass1.<HandleException>b__0()
   at Microsoft.Mashup.Client.ClientShared.UnexpectedExceptionHandler.HandleException(Exception e)
   at Microsoft.Mashup.Host.Document.ExceptionHandlerExtensions.HandleExceptions(IExceptionHandler exceptionHandler, Action action)
   at System.RuntimeMethodHandle.InvokeMethod(Object target, Object[] arguments, Signature sig, Boolean constructor)
   at System.Reflection.RuntimeMethodInfo.UnsafeInvokeInternal(Object obj, Object[] parameters, Object[] arguments)
   at System.Delegate.DynamicInvokeImpl(Object[] args)
   at System.Windows.Forms.Control.InvokeMarshaledCallbackDo(ThreadMethodEntry tme)
   at System.Windows.Forms.Control.InvokeMarshaledCallbackHelper(Object obj)
   at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
   at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
   at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
   at System.Windows.Forms.Control.InvokeMarshaledCallback(ThreadMethodEntry tme)
   at System.Windows.Forms.Control.InvokeMarshaledCallbacks()
   at System.Windows.Forms.Control.WndProc(Message& m)
   at System.Windows.Forms.NativeWindow.Callback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
   at System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG& msg)
   at System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG& msg)
   at System.Windows.Forms.Application.ComponentManager.System.Windows.Forms.UnsafeNativeMethods.IMsoComponentManager.FPushMessageLoop(IntPtr dwComponentID, Int32 reason, Int32 pvLoopData)
   at System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32 reason, ApplicationContext context)
   at System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32 reason, ApplicationContext context)
   at System.Windows.Forms.Form.ShowDialog(IWin32Window owner)
   at Microsoft.Mashup.Client.ClientShared.WindowManager.ShowDialog[T](T form, IWin32Window owner)
   at Microsoft.PowerBI.Client.Program.<>c__DisplayClassa.<Main>b__1()
   at Microsoft.Mashup.Host.Document.ExceptionHandlerExtensions.HandleExceptions(IExceptionHandler exceptionHandler, Action action)
   at Microsoft.PowerBI.Client.Program.Main(String[] args)

[Jaffa]

I have the same issue. I am also using UK dates. I specifically got it when filtering by 'this year' or 'last year' on a date column.

[MelissaSnell]

I have just upgraded Desktop and Gateway to the November release and the problem still exists

[MelissaSnell]

You can now log issues via Support.powerbi.com, but you will need a PowerBI Pro account.

[MelissaSnell · Accepted solution]

Right. I don't have a fix, but I DO have a work around.

What I did was this:

For every query where I had a datetime field that I was applying a date-time filter to e.g. "Date is Day -> yesterday" or "Week is Week -> this week" I removed the filter that was throwing the mashup error. I then inserted a step at the same point, which was to select the date-time column and add a new column and to select "Date", which gave me a new column that was just the date of the target column. I then applied the date filter to the new column and it worked without error.

This worked for both the Desktop report and for the scheduled or manual dataset refresh in the published report.