Hi there
I have an MDX problem that I need some help with,
hopefully someone out there can be that help...

I want a measure to be the number of populated cells in a
cube which can then get summed when aggregated.

I started with the following:

with member [Measures].[measure2] as
'IIF( [Measures].[measures1] > 0, 1, 0)'

which works for stuff at the leaf level but doesn't get
aggregated up over the hierarchy.
e.g. The following query
select measures.measure2 on columns,
time.month.members on rows
from MyCube
returns
Jan 1
Feb 1
Mar 1
Apr 1
May 1
etc...

but this isn't what I want. What I want is (for example)
if there are 14 days in [Jan] that have a value greater
than 0 for measure1, then ([Jan], [measure2]) should
return 14.

How do I do this?

Hopefully I've explained the problem succinctly enough. If
not, please let me know.

Regards and thanks in advance.
Jamie Thomson

Hi Jamie,

You should try something like :
Say for example that Day level is the leaf level of the time dimension :

with member [Measures].[Measures2] as
'Count(Filter(Descendants(Time.currentmember,Time. [Day]),Measures.[Measures1
]>0))'
select measures.measure2 on columns,
time.month.members on rows
from MyCube

HTH,

Lionel BILLON
MVP SQL Server
http://www.olapwebhouse.com




"Jamie Thomson" <jamie.thomson (AT) int21 (DOT) com> wrote