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,

 

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!!! :)

  • delia's avatar
    delia
    Frequent Visitor
    8 years ago

    I do have another small issue though....while the values all display now, the number values show up with a ".0" at the end. Since this new calculated column contains both text and numbers, I cannot use the modeling feature in Power BI to update it to a Whole Number. Any thoughts on how to get rid of the decimal at the end for WorkRequest[CurrentEstimate]? Thank u.

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

      Hi delia,

       

      Does WorkRequest[CurrentEstimate] have decimals? I can't reproduce it. Can you share some snapshots?

      switch_function2

       

       

       

      Best Regards,

      Dale

      • delia's avatar
        delia
        Frequent Visitor
        8 years ago

        Hi Dale, I should explain better. WorkRequest[CurrentEstimate] is a calculated column with Data Type currently set as Decimal. Its value is derived from columns whose Data Type is specified as Decimal. Is it okay if I change the Data Type of WorkRequest[CurrentEstimate] to a Whole Number? That should affect the Data Type of the other columns it is derived from right? Thanks again.