Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Error: Expressions that yield variant data-type cannot be used to define calculated columns.

How do I solve this issue? I would like to create this Calculated column. 

 

CompletionStatus =
SWITCH(
TRUE();
TasksOpportunities[Status] = "Open" && TasksOpportunities[Days until Completion]<0;"Open";
TasksOpportunities[Status] = "Open" && TasksOpportunities[Days until Completion]>=0 && TasksOpportunities[Days until Completion]<7;"Open";
TasksOpportunities[Status] = "Open" && TasksOpportunities[Days until Completion]>7;"Standby";
TasksOpportunities[Status] = "Completed"; 0;
TasksOpportunities[Status] = "Cancelled"; 3;
BLANK()
)

 

[Status] = Text

[Days until Completion] = Whole number

Need Help
  • v-juanli-msft's avatar
    v-juanli-msft
    7 years ago

    Hi Anonymous 

    Do you use the formula in a measure as below instead of a column?

    If so, please modify the formula as below to use in a measure

    CompletionStatus =
    SWITCH (
    TRUE (),
    MAX ( TasksOpportunities[Status] ) = "Open"
    && MAX ( TasksOpportunities[Days until Completion] ) < 0, "Open",
    MAX ( TasksOpportunities[Status] ) = "Open"
    && MAX ( TasksOpportunities[Days until Completion] ) >= 0
    && MAX ( TasksOpportunities[Days until Completion] ) < 7, "Open",
    MAX ( TasksOpportunities[Status] ) = "Open"
    && MAX ( TasksOpportunities[Days until Completion] ) > 7, "Standby",
    MAX ( TasksOpportunities[Status] ) = "Completed", "0",
    MAX ( TasksOpportunities[Status] ) = "Cancelled", "3",
    BLANK ()
    )

     

    Best Regards
    Maggie

     

    Community Support Team _ Maggie Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

3 Replies

  • v-juanli-msft's avatar
    v-juanli-msft
    Community Support
    7 years ago

    Hi Anonymous 

    Each result of the switch branch should be of same data type.

    Try this instead

    CompletionStatus =
SWITCH(
TRUE();
TasksOpportunities[Status] = "Open" && TasksOpportunities[Days until Completion]<0;"Open";
TasksOpportunities[Status] = "Open" && TasksOpportunities[Days until Completion]>=0 && TasksOpportunities[Days until Completion]<7;"Open";
TasksOpportunities[Status] = "Open" && TasksOpportunities[Days until Completion]>7;"Standby";
TasksOpportunities[Status] = "Completed"; "0";
TasksOpportunities[Status] = "Cancelled"; "3";
BLANK()
)

     

    Best Regards
    Maggie

     

    Community Support Team _ Maggie Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Hi Maggie,

      Thanks for the explanation, this totally makes sense. 

      Nevertheless I did get another error message when I tried your expression: 
      A single value for column 'Status' in table 'TasksOpportunities' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.

       

      Do you know why this is and how to solve it? 

      • v-juanli-msft's avatar
        v-juanli-msft
        Community Support
        7 years ago

        Hi Anonymous 

        Do you use the formula in a measure as below instead of a column?

        If so, please modify the formula as below to use in a measure

        CompletionStatus =
        SWITCH (
        TRUE (),
        MAX ( TasksOpportunities[Status] ) = "Open"
        && MAX ( TasksOpportunities[Days until Completion] ) < 0, "Open",
        MAX ( TasksOpportunities[Status] ) = "Open"
        && MAX ( TasksOpportunities[Days until Completion] ) >= 0
        && MAX ( TasksOpportunities[Days until Completion] ) < 7, "Open",
        MAX ( TasksOpportunities[Status] ) = "Open"
        && MAX ( TasksOpportunities[Days until Completion] ) > 7, "Standby",
        MAX ( TasksOpportunities[Status] ) = "Completed", "0",
        MAX ( TasksOpportunities[Status] ) = "Cancelled", "3",
        BLANK ()
        )

         

        Best Regards
        Maggie

         

        Community Support Team _ Maggie Li
        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.