filter({[Measures].[PPQ Count]},[Patient Stay].[PPQ Answer ID] <> 0)
---- this is a calculation in Analysis Service

PPQ Answer ID has a range from 0-4 and I'd like only those values which
are not 0.

PPQ Count is a measure where PPQ Answer ID is summed up


Unfortunately the Calculation doesn't work and shows the following
error msg.: function expects a string or a numeric but instead it gets
a tupel as an argument

anybody knows how to solve this.

backgroung is I#d like to calculate an average with all PPQ Answer IDs
which are not 0.

Thanks

There are a couple of problems with your filter statment, rather than go
into them - there is probably a better approach than using FILTER(),
which is to directly sum up the required values from the PPQ Answer ID
attribute.

You could either do this inclusively by adding up id's 1 to 4. (I am
assuming here that the ID is the key value for the attribute)

eg.

SUM({[Patient Stay].[PPQ Answer ID].&[1]:[Patient Stay].[PPQ Answer ID].
&[4]} ,[Measures].[PPQ Count])


But probably a more robust approach is to set the calculation up the
same way that you phrased the logic ie. get all the members except for
ID 0.

eg.

SUM(EXCEPT([Patient Stay].[PPQ Answer ID].[PPQ Answer ID].Members,
[Patient Stay].[PPQ Answer ID].&[0]) ,[Measures].[PPQ Count])



--
Regards
Darren Gosbell - SQL Server MVP
Blog: http://www.geekswithblogs.net/darrengosbell

In article <1155120521.449436.289770 (AT) i42g2000cwa (DOT) googlegroups.com>,
florian.stammer (AT) gmail (DOT) com says...

hi,

thanks for your help.

I tried:SUM(EXCEPT([Patient Stay].[PPQ Answer ID].[PPQ Answer
ID].Members, [Patient Stay].[PPQ Answer ID].&[0]) ,[Measures].[PPQ
Count])

but I got hierachie [PPQ Answer ID] was not found in the cube when
analysing the string [Patient Stay].[PPQ Answer ID].[PPQ Answer ID]

then I tried SUM(EXCEPT([Patient Stay].[PPQ Answer ID].Members,
[Patient Stay].[PPQ Answer ID].&[0]) ,[Measures].[PPQ Count])

but I got hierachie [PPQ Answer ID] was not found in the cube.

But maybe there is another way of solving my problem to get my
calculation right - I'll only need the correct [PPQ Count] the the one
without any PPQ Answer IDs who are 0. Isn't there an easy way to update
this in the properties window of the [PPQ Count] Measure - now only the
aggregate function is set to count - I would need count without 0.

Thanks



Darren Gosbell schrieb:

Is it possible that it doesn't work because [Patient Stay].[PPQ Answer]
is no Measure but part of a dimensional table.

I always get #value!




florian schrieb:

I could see that it was a dimension, but I don't know why it is saying
that the hierarchy cannot be found. I was just picking up the names from
the previous sample you provided.

--
Regards
Darren Gosbell - SQL Server MVP
Blog: http://www.geekswithblogs.net/darrengosbell

In article <1155132336.082600.186570 (AT) 75g2000cwc (DOT) googlegroups.com>,
florian.stammer (AT) gmail (DOT) com says...