Forum Discussion

ddeutschman's avatar
ddeutschman
Helper I
9 years ago

Getting an exception when creating a custom column

 

I am getting the following exception when attempting to create a Custom Column with the formula:

 

YTDCosts = CALCULATE(sum(udService_Costs[ActualCost]), YEAR(udService_Sales[Mth])=2017)

 

 

DataMashup.Trace Warning: 24579 : {"Start":"2017-04-12T16:28:28.0167655Z","Action":"JsonSerializer/TryDeserialize","InvalidJson":"{\"Expression\":\"CALCULATE(sum(udService_Costs[ActualCost]), YEAR(udService_Sales[Mth])=2017)\"}","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":6288,"Tid":1,"Duration":"00:00:00.0002082"}

 

This is the exact same syntax that I used with another table in the same file.  Here is the schema information for the table udService_Costs:

 

COLUMN_NAME DATA_TYPE CHARACTER_MAXIMUM_LENGTH NUMERIC_PRECISION DATETIME_PRECISION IS_NULLABLE
JCCo tinyint NULL 3 NULL NO
JCTransType varchar 2 NULL NULL NO
Contract varchar 10 NULL NULL YES
Department varchar 10 NULL NULL NO
udSALESMAN int NULL 10 NULL YES
Mth smalldatetime NULL NULL 0 NO
ActualDate smalldatetime NULL NULL 0 NO
ActualCost numeric NULL 12 NULL NO

Need Help

15 Replies

    • ddeutschman's avatar
      ddeutschman
      Helper I
      9 years ago

      Sorry, I am a beginner with the product.  What do you mean adding RELATED or RELATEDTABLE?  The columns used in the formula are in the same table.

       

      What I meant is that I used the same syntax for a custom column to calculate YTD Sales in another table.

       

      I should note that this is a View.  I performed a search on the two columns used in the formula and neither contain null values as you can see from the column definitions that both columns cannot contain NULL values.

      • v-huizhn-msft's avatar
        v-huizhn-msft
        Microsoft Employee
        9 years ago

        Hi ddeutschman,

        For your formula, you should add a filter function, the "YEAR(udService_Sales[Mth])=2017" return true or false, the CALCULATE can't recognise it. Please try the correct formula.

        YTDCosts=CALCULATE(sum(udService_Costs[ActualCost]), Filter(udService_Sales,YEAR(udService_Sales[Mth])=2017))

        You use same syntax in another table, and it returns the right result?

        Best Regards,
        Angelia