I have a cube with a measure 'Remaining Step Count' and a measure
Quantity.

The Remaining Step Count field is always non null and can be zero or
any other value.

The Quantity field is always 1.

In a test case covering a period of time, there are actually 283
remaining step count values that are > 0; 1420 are equal to zero.

I am trying to define a calculated measure in Analysis Service Manager
for this cube that will count the number of non zero values. In this
test case, I expect 283 as the result.

I tried this as the calculated measure but it always returns 1703 - the
total of zero and non zero values, not just the count of those with non
zero 'Remaining Step Counts'

IIF ( [Measures].[Remaining Step Count] > 0,[Measures].[Quantity],0)

I saw this suggested somewhere but it does the same thing, returning
1703:

IIF ( [Measures].[Remaining Step Count] = 0,NULL,[Measures].[Quantity])

I also tried this but it always returns 1:

IIF ( [Measures].[Remaining Step Count] = 0,NULL,1)

Any help in defining this calculated measure properly would be
appreciated.

this formula:
IIF ( [Measures].[Remaining Step Count] = 0,NULL,[Measures].[Quantity])

is evaluated in the current query context, NOT at the leaf level of the
cube.
if you want to apply this at the leaf level, create a new measure (not a
calculated one) based on this SQL statement:
case when Remaining_Step_Count_Column = 0 then null else Quantity_Column end

other option...
if you have a dimension "Step" in the cube, then you can use an MDX formula
like:
aggregate([Measures].[Quantity], Step.&[1]:Step.lastchild)

(you'll aggregate the quantities only for step starting at 1 to the last
member; syntax to validate)


"Tiger Tail" <Larry_Uzzel (AT) hotmail (DOT) com> wrote

Thanks for the assist.

I defined 2 new measures (not calculated) - one that would properly sum
the values and another that would properly count the values.

I was then able to do a calculated measure using them and it works
fine.



Jéjé wrote: