Very new to MDX, and stuck trying to understand how to achieve the following; would appreciate some hints in the right direction please.

Using two dimensions - Project and Department, I am trying to retrieve a count of Projects opened by each Department.

I can retrieve a total count of Projects; when it comes to breaking them out by Department, however, I'm running into problems.  My Project dimension has a Project.Department hierarchy, and I toyed with using that; my Department dimension is role-playing (as far as I understand that term), and that is the one I am using in the sample below.

I won't go through the permutations of everything I've tried so far, but currently, as a base, I have the following:

WITH
MEMBER [Measures].[Project Count] AS COUNT({[Project].[Project].Children})

SELECT
{ [Measures].[Project Count] }
on 0,

NON EMPTY
{ [Department].[Department].Children }
on 1

FROM [MyCube]

Desired result-set would look like:

DEPT         PROJECT COUNT
Dept 1       10
Dept 2       15
Dept 3        8

My current code gives me the total count of all Projects, on every Department row, rather than a count per Department - so I'm missing something basic here.  A kickstart in the right direction would be welcomed; I've read much, but understood little!

Many thanks

Hi Cate,

Try if this works :

WITH
MEMBER [Measures].[Project Count] AS COUNT({strtomember('[Project].[Department]'+right([Department].[Department].CurrentMember.uniquename,
len([Department].[Department].CurrentMember.uniquename)-25))})

SELECT 
{ [Measures].[Project Count] }
on 0,

NON EMPTY
{ [Department].[Department].Children }
on 1

FROM [MyCube]