ElseIf / CASE ELSE

Questions & Answers
1

Hello everyone,
EazyBI is very powerful tool and I am quite new user. Therefore, I would like to ask you for benevolence.

I would like to check if there is any options to use ElseIf (or equivalent) in formulas? I have tried to use ELSE CASE or CASE ELSE and none worked. So far I had to create cascading CASE WHEN statements, but it is usually not best practice.

Here is example:

CASE WHEN [Client].Currentmember.Name = "Client A"
    THEN 1
  ELSE 
    CASE WHEN [Client].Currentmember.Name = "Client B" 
      THEN 2
    ELSE 
      CASE WHEN [Client].Currentmember.Name = "Client C"
        THEN 3 
    END 
  END
END
2

Hi,

You may want to try Case when in little bit different way here:

CASE [Client].Currentmember.Name
WHEN "Client A" THEN 1
WHEN "Client B" THEN 2
WHEN "Client C" THEN 3
END

Martins / eazyBI support

3

This is much better, thank you. Is there any option if subsquent conditions should contain different variables or comparing methods. Is it possible to optimize following code?

CASE WHEN [Client].Currentmember.Name = "Client A"
    THEN 1
  ELSE 
    CASE WHEN [Author].Currentmember.Name = "Author A" 
      THEN 2
    ELSE 
      CASE WHEN [Client].Currentmember.Name <> "Client C"
        THEN 3 
    END 
  END
END
4

Hi,

In such case, you should use the approach you already found.

Martins / eazyBI support

5

What if I want that search with wildcard?, for example:

WHEN “Client South.*” THEN 10

What’s the syntax on that case? Thanks!

6

Hi,

Try this code in your case:

CASE 
WHEN [Client].Currentmember.Name matches "Client South.*"
THEN 10
END

.* at the end of the string would search any symbol in the following string characthers

Martins / eazyBI support

7

Hi,
If I want to aggregate certain values of field A based on specific value of field B, is the following code okay? because I am getting a syntax error:

CASE [Field B].CurrentMember.Name
WHEN ValueOfFieldB
THEN
Aggregate ({[FieldA]. [Value1], [FieldA]. [Value2], [FieldA]. [Value3]})

8

@nadavrab4488

What is the exact syntax you use, and what error do you get?

Martins

9

Hello Martins,

I am trying to find the sum of business value and freeze business value fields of all the epics that their fix version = PI7 and their status is not done or ready to deploy.
Exact syntax:
CASE WHEN (
[Issue].CurrentMember.get(‘Fix Version’) = “PI7 (08/01-06/04)” AND
(NOT([Issue].CurrentMember.get(‘Status’) = ‘Done’) AND
NOT([Issue.CurrentMember.get(‘Status’) = ‘Ready to deploy’))
)
THEN Aggregate ({[Freeze Feature Business value], [Feature Business value]})

The error I am getting -
Formula is not valid: Syntax error at line 6 column 77. token “”

10

Your Case statement is missing the END in the formula
https://docs.eazybi.com/eazybi/analyze-and-visualize/calculated-measures-and-members/mdx-function-reference/case-statement

Martins / eazyBI