Cube returns all ancestor siblings if member cant be found in hierarchy.

[metalray]

Hello Friends of SAS,

Below we have the nifty MDX filter expressions that gets all descendants from 2 levels above where the user id can be found in the hierarchy.
The MDX works but it gets all the siblings as well not only the main agency that the user is actually in (at bottom level).

General Agency
------Main Agency
---------Team
------------Seller_ID

with Acensors.....2 we want to go 2 levels up and get only the Main Agency
the seller is part of and not all main agencies-

First option of MDX:

Descendants(
Ancestor(
Head(
Filter(
[DIM_
Sales].AllMembers,[DIM_Sales].CurrentMember.Level.Name = Seller_ID' and [DIM_Sales].CurrentMember.Name = 'SUB::SAS.Userid')
).Item(0

)
,2)
)

Second option of MDX:

Generate
(
Filter
(
  [Dim_Sales].ALLMEMBERS
  ,[Dim_Sales].CurrentMember.LEVEL.NAME = 'Seller_ID'
  AND
  [Dim_Sales].CurrentMember.Name = 'SUB::SAS.Userid'
)
,Descendants
(
  Ancestor([Dim_Sales].CurrentMember,2)
)

)

Problem:
We noticed that if the user is in the hierarchy SAS gets/returns everything (even the main agency the user is not part of).
Both MDX Filters behave the same.

So the user will be able to see not only all the descendants of his
agency (2 levels above) but can also see all the other agencies.

How would an enhanced MDX statement look that only the main agencies the userID is in the hierarchy (instead of everything).

Thanks,
Bob

[metalray]

We dont have to problem with the team level. A team lead, also part of the level, sees only his/her team and not the other teams that are silbings to that team.

Team (specific user group) MDX:

Descendants(
Ancestor(
Head(
Filter(
[DIM_
Sales].AllMembers,[DIM_Sales].CurrentMember.Level.Name = Seller_ID' and [DIM_Sales].CurrentMember.Name = 'SUB::SAS.Userid')
).Item(0

)
,1)
)

[metalray · Accepted solution]

We figured out that there was an error in the hierarchy.