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,...
  • 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

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

      • delia's avatar
        delia
        Frequent Visitor
        8 years ago

        Awesome, that did it! Thanks Dale!!! :)