Measure: Calculate customers for a city

Category: sql server analysisservices


Sonnfan on Sun, 30 Dec 2012 17:34:08

Hi everybody, I need some help in creating a measure in MDX.

My customer dimension looks like that:

|--------Street number

So for example

|-----------Customer A
|-----------Customer B
|-----------Customer C
|-----------Customer D

Now I'd like to create a measure, that gives me to every level the amount of customers, for example '4' for Munich (and something like 10 for Bavaria).

I read about the count function but can't seem to make it work properly. Could somebody tell my how to implement this feature? :)

Regards and best wishes,



Martin Mason on Sun, 30 Dec 2012 19:24:08

The following definition should work.

CREATE MEMBER CurrentCube.[Measures].[CustomerCount] AS
	COUNT(Descendants( [Customer].[Hierarchy].CurrentMember, [Customer].[Hierarchy].[Customer] ) )

HTH, Martin

Sonnfan on Tue, 01 Jan 2013 17:49:53

Hey, thanks for your suggestion. I was able to solve the Problem with the following code:

with member [Measures].[Amount] as 'sum(descendants([Customer].currentmember, [Customer].[Name]),1) '