How to exclude 1 member from all dimension values

­­General Discussions
1

Hi,

in my cube i have a dimension called “Dimension3”.
In the modelmanager i have defined multiple possible values linked to this dimension. (“A”,“B”
,…“Z”). Is there a possiblity to define a “named set” that returns the sum of all values for this dimension excluding a particular dimension (for instance “A”).
Now i have to calculate this seperately in the reports by subtracting the values related to dimension3 “A” from the totals. But it would be useful to do this in a smarter way.

2

You can use Filter in Nameset which should exclude your dimension3.&[account] .

I think the formula would be something e.g.
FILTER(Descendants(dimension3].default.[All],leaves),
([dimension3].default.&[Account to remove] ))

this should filter out the the account.

3

Hello Ali,
Thanks for your suggestion.

i tried the following formula
FILTER(Descendants([dimension3].default.[All],leaves), ([dimension3].default.&[FTE1-2] ))

in the MDX expresssion box, but there is a syntax error somewhere. Can you help me out here ?

4

Hi Bram,

I think there are two commas before leave, I might have missed one while typing, my apologies.

Regards,

Ali Arsalan

5

HI Ali,

i also tried the following:
FILTER(Descendants(dimension3].default.[All],leaves),
([dimension3].default.&[FTE1-2] ))
but it still will not accept this formula either.
Any other suggestion ?
Thanks for your feedback.

6

I find another alternative where you filter by its name rather than ID, I was having issue on ID myself, hence this worked for me.

Account-Opex is my dimension

FILTER( ADDCALCULATEDMEMBERS( DESCENDANTS([Account-Opex].[Default].&[DoE].Children) ), ( NOT INSTR( [Account-Opex].[Default].CurrentMember.Name, “Dep” ) > 0 ) )