cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
delia Frequent Visitor
Frequent Visitor

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

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Support Team
Community Support Team

Re: switch function

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

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

Re: switch function

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

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

Re: switch function

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.

Highlighted
Community Support Team
Community Support Team

Re: switch function

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

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

Re: switch function

Awesome, that did it! Thanks Dale!!! Smiley Happy

delia Frequent Visitor
Frequent Visitor

Re: switch function

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.

Community Support Team
Community Support Team

Re: switch function

Hi @delia,

 

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

switch_function2

 

 

 

Best Regards,

Dale

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

Re: switch function

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.

Community Support Team
Community Support Team

Re: switch function

Hi @delia,

 

According to my test, the Date type Whole number will truncate the decimals. For instance, 121.33 will be 121. You can do it if the degree of accuracy isn't a big deal. There could be a workaround: creating anther column.

 

Best Regards,

Dale

 

 

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