Hi,

I have this cube:
a measure 'Test'
a dimension 'Location'

the location dimension looks like this:
Global
APAC
China
Singapore
EMEA
Belgium
Spain
OTHER

I made a calculated Cell, that when i roll up on second level (APAC, EMEA,
OTHER), the measure should show NULL. But i want to exclude 'OTHER', so the
NULL only needs to be shown for APAC and EMEA. I have tried the filter:

Filter({[Location]},[Location].Currentmember.Name="OTHER")

But this gives an error. It says that it cannot convert the set to an
expression. In examples i see that in place of the conditional expression
there should be a measure instead of a dimension. So i guess i cannot use the
filter???

How can i achieve to filter 'OTHER'?

Thnx in advance

Stanley

First of all, {[Location]} means {[Location].CurrentMember}. I guess it is
not what you wanted.

If you want to select only OTHER, just {[OTHER]} is enough. If you want to
exclude OTHER, Except(SecondLevel.Members, {[OTHER]}) will be useful.

Ohjoo Kwon


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

Hi Ohjoo,

thnx for the response, but this one gives me the same error also. And
according to the help the except finds differences between 2 sets, it does
not exclude a member.

Thnx,

Stanley

"Ohjoo Kwon" wrote:

Here difference means difference set.

In my sample, SecondLevel.Members returns all members, {APAC, EMEA, OTHER},
at second level. [Other] is a member at second level too, so it is included
in the result set.

Finally, Except(SecondLevel.Members, {[OTHER]}) should returns all members
at second level without [Other] member. In other words, {APAC, EMEA}

Ohjoo



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

In the MDX sample application it works:

select {[Measures].[Sales]} on columns,
{Except([Location].[Level 02].members,{[OTHER]})} on rows
from Sales

I don't see 'OTHER'

But when i use only this part:

Except([Location].[Level 02].members,{[OTH]})

as a condition for the calculated cell, it doesn't work. That's my obstacle
actually.

thnx,

Stanley

"Ohjoo Kwon" wrote:

Except() returns Set not Boolean.

What is your exact condition? Not {OTHER}? If then,

NOT Location.CurrentMember IS [OTHER]

Ohjoo

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

Ok Ohjoo, this works. I had tried something like this before:

NOT(Location.CurrentMember.name IS "OTHER")

But i didn't know that OTHER had to be between brackets instead of quotes.

Thank you very much.

regards,

Stanley


"Ohjoo Kwon" wrote: