Forum Discussion

delia's avatar
delia
Frequent Visitor
8 years ago
Solved

switch function

Hi, I have a switch function as follows:

EstDesc = Switch ( TRUE (),
 WorkRequest[CurrentEstimate] = 97,"Small",
 WorkRequest[CurrentEstimate] = 497,"Medium",
 WorkRequest[CurrentEstimate] = 997,"Large",
 WorkRequest[CurrentEstimate] = 1997,"X-Large")

 

I want to add a catch all to the end that would just return WorkRequest[CurrentEstimate], but the below is throwing an error.

EstDesc = Switch ( TRUE (),
 WorkRequest[CurrentEstimate] = 97,"Small",
 WorkRequest[CurrentEstimate] = 497,"Medium",
 WorkRequest[CurrentEstimate] = 997,"Large",
 WorkRequest[CurrentEstimate] = 1997,"X-Large",

WorkRequest[CurrentEstimate])

 

Error is: Expressions that yield variant data type cannot be used to define calculated columns. Please advise. Thanks

  • v-jiascu-msft's avatar
    v-jiascu-msft
    8 years ago

    Hi delia,

     

    That's a limitation of Direct Query. Please try it like below.

    EstDesc =
SWITCH (
    TRUE (),
    WorkRequest[CurrentEstimate] = 97, "Small",
    WorkRequest[CurrentEstimate] = 497, "Medium",
    WorkRequest[CurrentEstimate] = 997, "Large",
    WorkRequest[CurrentEstimate] = 1997, "X-Large",
    "" & WorkRequest[CurrentEstimate]
)

    switch_function

     

    Best Regards,

    Dale

8 Replies

  • v-jiascu-msft's avatar
    v-jiascu-msft
    Microsoft Employee
    8 years ago

    Hi delia,

     

    The WorkRequest[CurrentEstimate] must be a numerical column. The root cause is the type of the results are mismatched, Text and Numbers. You can modify it like below.

    EstDesc =
SWITCH (
    TRUE (),
    WorkRequest[CurrentEstimate] = 97, "Small",
    WorkRequest[CurrentEstimate] = 497, "Medium",
    WorkRequest[CurrentEstimate] = 997, "Large",
    WorkRequest[CurrentEstimate] = 1997, "X-Large",
    FORMAT ( WorkRequest[CurrentEstimate], "" )
)

    Best Regards,

    Dale

    • delia's avatar
      delia
      Frequent Visitor
      8 years ago

      Hi v-jiascu-msft, you are correct that WorkRequest[CurrentEstimate] is a numerical column. I tried using the FORMAT function (thanks for the suggestion) but received this error:

      Function FORMAT is not allowed as part of calculated column DAX expressions on DirectQuery models.

      • v-jiascu-msft's avatar
        v-jiascu-msft
        Microsoft Employee
        8 years ago

        Hi delia,

         

        That's a limitation of Direct Query. Please try it like below.

        EstDesc =
SWITCH (
    TRUE (),
    WorkRequest[CurrentEstimate] = 97, "Small",
    WorkRequest[CurrentEstimate] = 497, "Medium",
    WorkRequest[CurrentEstimate] = 997, "Large",
    WorkRequest[CurrentEstimate] = 1997, "X-Large",
    "" & WorkRequest[CurrentEstimate]
)

        switch_function

         

        Best Regards,

        Dale