I'm having a problem with the distinct count function and I'm hoping I can
find some help in this forum.

What I have is a cube where I create a calculated member called stack count
using the distinctcount( dimmension.members) function.

This returns the correct counts that I'm expecting with the following
exception.

The cube has a time dimmension and and a dimmension that returns an operator
id.

When the cube is generated, if an operator does not have any stakcs on a
given day, the distinctcount function returns 0. My customer wants the cube
to hide any value less then 1.

I'm stumped. I can not figure out how to display a distinctcount > 1.

Any help is very much appreciated.

Thanks

Tim

Can you just test and replace 0 with Null?

iif(distinctcount(dimmension.members) = 0,
Null, distinctcount(dimmension.members))


- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.developersdex.com ***

Using the Analysis manager I entered this text for the calculated member:

iif(distinctcount([Rescan Stack Id].members) = 0,

Null, distinctcount([Rescan Stack Id].members))



When I use the check button The analysis manager returns this message:



Formula error - the DistinctCount function can only be used as a
top-level(most outer) function in a calculated measure definition - in the
DistinctCount function



Any other suggestions?



Thanks

Tim



"Deepak Puri" <deepak_puri (AT) progressive (DOT) com> wrote

I have found a solution to my DistinctCount issue.

The following MDX gave me the appropriate counts:

iif(Count(NonEmptyCrossJoin( {[Rescan Stack ID].members},

Descendants([Scan Operator
Id].CurrentMember,1,BEFORE_AND_AFTER)))=0,NULL,Count( NonEmptyCrossJoin(
{[Rescan Stack ID].members},

Descendants([Scan Operator Id].CurrentMember,1,BEFORE_AND_AFTER)))

)





Tim



"Tim Graham" <tgraham3 (AT) metlife (DOT) com> wrote

Any info on Outlooksofts application....?


"Tim Graham" <tgraham3 (AT) metlife (DOT) com> wrote