switch function

[delia]

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]

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]

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 · Accepted solution]

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]
)

Best Regards,

Dale

[delia]

Awesome, that did it! Thanks Dale!!! 🙂

[delia]

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]

Hi @delia,

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

Best Regards,

Dale

[delia]

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.

[v-jiascu-msft]

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