locked
Formula with semantic error. Need help to figure out why this happens now. RRS feed

  • Question

  • Hello all,

    I have a formula that has been working for 7 months, but doesn't work anymore. 

    Error: Semantic Error. Coparison operations cannot be performed on values of incaptible types. Ensure that both values are Numeric, String, or Boolean (True/False). Consider using the VALUE OR FORMAT fucntion to convert one of the values. 

    The formula is the following one: 

    =CALCULATE(SUM(Table1[Field1]),ALL(Table1[FY],Table 1[Month]),Table1[Date]="7/15/2015")/CALCULATE(SUM(Table1[Field2]),ALL(Table[FY],Table1[Month]),Table1[Date]="7/15/2015")

    I checked an old excel document with no updated database and the same error shows up. An error that wasn't happening before. 

    It looks like the error is in the Table1[Date]="7/15/2015", but I can figure out how to solve it.

    Thank you for your help.

    Monday, February 29, 2016 12:08 PM

Answers

  • It looks like your data is coming thru in European format of d/m/yy but Power Pivot is expecting m/d/yy.

    So there are different ways to fix this but it will depend on whether or not this is a one off type in the DB or is the entire Date column formatted like that, etc.?

    Is the format consistent for the entire column?  For example are the day and month always 2 digits and is the year always 4?

    If its consistent for the whole column then something like this might work:

    =DATE(RIGHT([Date], 4), MID([Date],4, 2), LEFT([Date], 2))
    If the column contains a date, it is best to make Power Pivot treat it like a date and not text to make sure you always get expected behavior.

    • Proposed as answer by Charlie Liao Monday, March 7, 2016 1:34 AM
    • Marked as answer by Charlie Liao Monday, March 7, 2016 9:33 AM
    Tuesday, March 1, 2016 2:36 PM
    Answerer

All replies

  • This error usually means you are comparing text to a date or text to a number etc.

    What probably happened is that a "rogue" value (or maybe more than one) slipped into your data source that now causes the error.

    Here are some things to check:

    What is the format of Table1[Date]?  Text or Date format in Power Pivot.  Putting the date in double quotes makes it text.

    Are all the dates in that some column in the data source valid dates?  For example I have seen things like 7/15/2105 due to a typo

    If Table1[Date] is a date the it is probably better to use the DATE function of PowerPivot to check it.

    For example:

    Table1[Date] = DATE(2015, 7, 15)

    This helps ensure that you are comparing a date to a date.

    There are really quite a few things that might have broken the formula but these are some of the more common things that I have seen.

    I would focus on whether something changed in the data source that broke references to this column.

    Monday, February 29, 2016 7:26 PM
    Answerer
  • Thank you very much.

    After investigating more I realized that somehow the date changed the format from text to date, the problem is that it can't be changed back. I get the following error.

    ============================
    Error Message:
    ============================

    The following system error occurred:  Type mismatch.
    Cannot convert value '15/07/2015' of type Text to type Number.

    ----------------------------
    The following system error occurred:  Type mismatch.
    Cannot convert value '15/07/2015' of type Text to type Number.


    ============================
    Call Stack:
    ============================

       at Microsoft.AnalysisServices.BackEnd.DataModelingSandbox.ExecuteCaptureLogAndProcessResults(OperationType type, Boolean cancellable, Boolean raw, Boolean raiseEvents)
       at Microsoft.AnalysisServices.BackEnd.DataModelingSandbox.ExecuteAMOCode(OperationType type, OperationCancellability cancellable, AMOCode code, Boolean raiseEvents)
       at Microsoft.AnalysisServices.BackEnd.SandboxTransaction.CommitUpdates()
       at Microsoft.AnalysisServices.BackEnd.SandboxTransaction.CommitInternal(Boolean finalCommit)
    ----------------------------
       at Microsoft.AnalysisServices.BackEnd.SandboxTransaction.CommitInternal(Boolean finalCommit)
       at Microsoft.AnalysisServices.Common.SandboxEditor.DataTypeSelectionChanged(String newSelectedString, TableWidgetGridControl grid, List`1 selectedColumns)

    ============================

     Do you know a way to change the format or I have to redo all the tab?

    Thank you very much

    Tuesday, March 1, 2016 11:26 AM
  • It looks like your data is coming thru in European format of d/m/yy but Power Pivot is expecting m/d/yy.

    So there are different ways to fix this but it will depend on whether or not this is a one off type in the DB or is the entire Date column formatted like that, etc.?

    Is the format consistent for the entire column?  For example are the day and month always 2 digits and is the year always 4?

    If its consistent for the whole column then something like this might work:

    =DATE(RIGHT([Date], 4), MID([Date],4, 2), LEFT([Date], 2))
    If the column contains a date, it is best to make Power Pivot treat it like a date and not text to make sure you always get expected behavior.

    • Proposed as answer by Charlie Liao Monday, March 7, 2016 1:34 AM
    • Marked as answer by Charlie Liao Monday, March 7, 2016 9:33 AM
    Tuesday, March 1, 2016 2:36 PM
    Answerer