Forum Discussion
Getting an exception when creating a custom column
Hi ddeutschman,
I test using your part of sample data, it works fine. Why you return an error? Could you please post the error message or screenshot?
test = CALCULATE(SUM(udService_Costs[ActualCost]),FILTER(udService_Costs,YEAR(udService_Costs[Mth])=2002))
Thanks,
Angelia
This is the message that is shown:
error message
and from the trace:
DataMashup.Trace Warning: 24579 : {"Start":"2017-04-27T16:55:19.0857303Z","Action":"JsonSerializer/TryDeserialize","InvalidJson":"{\"Expression\":\"CALCULATE(sum(udService_Costs[ActualCost]), YEAR(udService_Sales[Mth])=2002)\"}","Exception":"Exception:\r\nExceptionType: System.ArgumentNullException, mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089\r\nMessage: Value cannot be null.\r\nParameter name: Formula\r\nStackTrace:\n at Microsoft.Mashup.Host.Models.Verify.NotNull[T](T value, String argumentName)\r\n at Microsoft.Mashup.Host.Document.JsonSerializer.<>c__DisplayClass4`1.<Deserialize>b__3()\r\n at Microsoft.Mashup.Host.Document.JsonSerializer.HandleDeserializationExceptions[T](Func`1 action, String json, Boolean includeValueInException)\r\n at Microsoft.Mashup.Host.Document.JsonSerializer.TryDeserialize[T](String json, T& value)\r\n\r\n\r\n","Result":false,"ProductVersion":"2.45.4704.442 (PBIDesktop)","ActivityId":"00000000-0000-0000-0000-000000000000","Process":"PBIDesktop","Pid":8112,"Tid":43,"Duration":"00:00:00.0420657"}
DataMashup.Trace Information: 24579 : {"Start":"2017-04-27T16:55:19.1279646Z","Action":"JsonSerializer/TryDeserialize","Result":true,"ProductVersion":"2.45.4704.442 (PBIDesktop)","ActivityId":"00000000-0000-0000-0000-000000000000","Process":"PBIDesktop","Pid":8112,"Tid":43,"Duration":"00:00:00.0001169"}
DataMashup.Trace Warning: 24579 : {"Start":"2017-04-27T16:55:21.8308957Z","Action":"JsonSerializer/TryDeserialize","InvalidJson":"{\"Expression\":\"CALCULATE(sum(udService_Costs[ActualCost]), YEAR(udService_Sales[Mth])=2002)\"}","Exception":"Exception:\r\nExceptionType: System.ArgumentNullException, mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089\r\nMessage: Value cannot be null.\r\nParameter name: Formula\r\nStackTrace:\n at Microsoft.Mashup.Host.Models.Verify.NotNull[T](T value, String argumentName)\r\n at Microsoft.Mashup.Host.Document.JsonSerializer.<>c__DisplayClass4`1.<Deserialize>b__3()\r\n at Microsoft.Mashup.Host.Document.JsonSerializer.HandleDeserializationExceptions[T](Func`1 action, String json, Boolean includeValueInException)\r\n at Microsoft.Mashup.Host.Document.JsonSerializer.TryDeserialize[T](String json, T& value)\r\n\r\n\r\n","Result":false,"ProductVersion":"2.45.4704.442 (PBIDesktop)","ActivityId":"00000000-0000-0000-0000-000000000000","Process":"PBIDesktop","Pid":8112,"Tid":1,"Duration":"00:00:00.0002326"}
Hi ddeutschman,
From your screenshot, you load the data from SQL Server in DirectQuery model, there are some limitations, please check them in the article. You'd better load data using "Import" model.
Best Regards,
AngeliaThere are over a million transactions in that table. It is not practical to use the import model.
I should also point out that the exact same calculation is being used to calculate the YTD revenue and that calculation is not failing.
I made a copy of the dashboard file and chnaged the input mode to Import. Same error is occurring.
I need to get this project moving forward. If you need to have access to the file, I will upload it for you to review. I have filtered out the sensitive columns from the tables.
Hi ddeutschman,
I really understand you, I suggest you'd better extract and handle your resource table according to your needs using SQL statement, then load the result table to Power BI desktop to visual your data.
Best Regards,
Angelia