Chris Webb's BI Blog

Analysis Services, MDX, PowerPivot, DAX and anything BI-related

CASE statements

with one comment

One of the first things I noticed when I got hold of AS2005 for the first time, well over a year ago now, was that we now had a CASE statement in MDX. The joy! And yet it’s one of those small improvements which so often gets ignored, so I thought I’d mention it. Here’s an example:

WITH
MEMBER MEASURES.MYTEST AS
CASE
WHEN [Measures].[Internet Sales Amount]> 3780000 THEN "HIGH"
WHEN [Measures].[Internet Sales Amount]> 3700000 THEN "MEDIUM"
ELSE "LOW"
END

SELECT {[Measures].[Internet Sales Amount],MEASURES.MYTEST} ON 0,
[Date].[Day of Week].[Day of Week].MEMBERS ON 1
FROM [Adventure Works]

No more nested IIFs! One of the biggest contributions to improved productivity in AS2005 in my opinion…

Written by Chris Webb

June 7, 2005 at 12:01 pm

Posted in MDX

One Response

Subscribe to comments with RSS.

  1. Oh thank you!! It is about time for that one.Half of the measures I end up creating have so many nested iif statements the code is unreadable.

    Christopher

    June 7, 2005 at 8:22 pm


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 2,866 other followers

%d bloggers like this: