Error when using CASE WHEN to convert text to number

Questions & Answers
1

I am getting an error message when using the following CASE WHEN statement.

CASE [Measures].[Issue Risk consequence].Value
WHEN "Trivial" THEN 1
WHEN "Low" THEN 2
WHEN "Medium" THEN 3
WHEN "High" THEN 4
WHEN "Severe" THEN 5
END

image
image765×551 20.5 KB

I get the same error if I remove the .Value from line 1.

I was able to get what I want by using a nested IIF statement (see below) but am wondering what I am doing wrong with the CASE statement.

Here is my workaround…

IIf([Measures].[Issue Risk consequence] = "Trivial",1,
  IIf([Measures].[Issue Risk consequence] = "Low",2,
    IIf([Measures].[Issue Risk consequence] = "Medium",3,
      IIf([Measures].[Issue Risk consequence] = "High",4,
        IIf([Measures].[Issue Risk consequence] = "Severe",5,0
        )
      )
    )
  )
)```
2

Hi @wojo3

Please try this formula:

CASE [Issue].CurrentHierarchyMember.Get('Risk consequence')
WHEN "Trivial" THEN 1
WHEN "Low" THEN 2
WHEN "Medium" THEN 3
WHEN "High" THEN 4
WHEN "Severe" THEN 5
END

​Best regards,
​Nauris

3

Thank you! It worked perfectly.
Still plenty to learn with eazyBI…

4

Glad it works!

The CASE statement is a picky function, which expects the arguments of the same type to be compared.

In this case, it expected a string type argument coming in, so, for issue properties like the [Measures].[Issue Risk consequence] member, you can always click the “show” button in the Measures dimension, copy the original request for the string, and use that instead.