Hi All

I want the distinct count of employee in organization till current date
(from first member of year level upto currently selected year).
I have MSAS 2000.

I have dimensions:
Time
Action(Hire,terminated,Promotion)
Age
location
Company-Dept
Grade
employee


Plz Help me.

try something like:
count(nonemptycrossjoin(employes.employeeID.member s,
{time.year.members(0):time.currentmember}, measures.[anycount or sum
measure],1))

you have to count the number of members in the employe dimension and then do
a dynamic distinct count calculation.

"Hash" <harshal.patil (AT) sify (DOT) com> wrote

Jeje,
I have a similar problem I want to count the number of clients (accounts)
for any given dimension or filter.

COUNT({[Account Name].[Account Name].MEMBERS }, Excludeempty)

However I am getting #ERR as the value. At first without stating the
excludeempty I got a value but it wasn't dynamic when I changed the filter
(dice/slice) in the cube browser. Any ideas?

"Jéjé" wrote:

you are right, I have ommit the exclude_empty keyword...

your formula count the number of account without specifying any measure to
restrict the list
try:
COUNT(
crossjoin([Account Name].[Account Name].MEMBERS, measures.[anycountmeasure])
, Excludeempty)

this is good but slow with large amount of accounts.
this one perform better, but can provide unexpected results when the user
use the pivottable, due to the cache:
COUNT(
crossjoin(
nonemptycrossjoin([Account Name].[Account Name].MEMBERS,
{measures.[anycountmeasure]},1)
,measures.[anycountmeasure])
, Excludeempty)

(make sure that the cube with the count measure is aggregated at the lowest
level for the accounts and the higher level for the other dimension)

also you can greatly improve the performance of these calculation by palying
with the cache ratio and cache policy option in your connection string:
cache policy=7;cache ratio=0.001;data source=myserver....

"SAM" <SAM (AT) discussions (DOT) microsoft.com> wrote

Hi Jeje thanks. Is there a way when there are no values to return a blank
instead of 0?

"Jéjé" wrote:

create 2 calculated measures, the first one will do the calculation (and
will be hidden)
the second one will do a IIF(firstmeasure = 0, null, firstmeasure)

"SAM" <SAM (AT) discussions (DOT) microsoft.com> wrote

Hi Jeje,

I am trying to use the second formula that you suggested for the performance
because you were right, performance is slow. However, I am gettiing 0 zero
results for the second one. Any ideas?

"Jéjé" wrote: