**CASE**

**WHEN Measures.[MyCalc]=0 THEN 0**

**WHEN Measures.[MyCalc]<=5 THEN 5**

**WHEN Measures.[MyCalc]<=10 THEN 10**

**WHEN Measures.[MyCalc]<=15 THEN 15**

**WHEN Measures.[MyCalc]<=20 THEN 20**

**WHEN Measures.[MyCalc]<=25 THEN 25**

**...**

**ELSE 100**

**END**

What I have found is that Measures.[MyCalc] is recalculated for every WHEN clause until it drops out. So the CASE statement could be rewritten as below with much greater performance. If your calculations are parsing through 20 WHEN clauses, this new expression could be 20 times faster! This is most noticeable when MyCalc is itself expensive, particularly when it is FE (formula engine) intensive.

**CASE Round(Measures.[MyCalc]/5)*5**

**WHEN 0 THEN 0**

**WHEN 5 THEN 5**

**WHEN 10 THEN 10**

**WHEN 15 THEN 15**

**WHEN 20 THEN 20**

**WHEN 25 THEN 25**

**...**

**ELSE 100**

**END**

Note; you cannot put < or > operators in the WHEN statements if you use the second format above. However, with a little arithmetic, you might be able to bracket the calculation as I have done above using Round().

Another note; if might not be possible to express the CASE statement as I suggest above because there are several conditions. However, I encourage you to think laterally, as you might be able to nest a few CASE statements on the various conditions, so there are fewer calcs per CASE statement, making perform much better.

## No comments:

Post a Comment