Filtering tuples out of set based on cell value

Question

I want to create a custom hierarchy in my query (I can’t add it easily to the cube due to deployment / dev changes, etc.) for:

Month -> Week -> Date

I am trying to do this by creating a set as below which does generate the elements in the set I’m looking for, but I see elements that I don’t want in my set (bolded below). Any suggestions for how to remove these elements from my set? I tried using the "CurrentMember" function and filtering out based on "Member_Name", but it doesn't seem to have the same value for a given row as what is shown in SSMS / shown below.

Thanks!

NonEmpty(CrossJoin(
   [Event Received Date].[Month].[All].Children
  , [Event Received Date].[Week].Members
  , [Event Received Date].[Date].Members
  )
 )

Month	Week	Date
December	All	12/18/2011
December	12/25/2011 - 12/31/2011	All
December	12/25/2011 - 12/31/2011	12/25/2011

Answer 1

Hi  JR Davis,

Can you use the below script.

NonEmpty(CrossJoin(
   [Event Received Date].[Month].[All].Children
  , [Event Received Date].[Week].children
  , [Event Received Date].[Date].Members
  )
 ) 

If you need to remove "ALL" level from the other attributes also kindly follow the same.

---

Rakesh M J | MCTS,MCITP ( SQL SERVER 2008 )

Dont forget to mark it as Answered if found useful | myspeakonbi

Answer 2

Thanks, Rakesh. I tried that, but then it also removed a row that I want from my set. Is there a way for me to have the non-highlighted row below (i.e. I want one row where Week="All" for every month in the set as well as one row ) and exclude the bolded row?

Month  Week   Date
December All   All
December All   12/04/11

Answer 3

Hi,

Would

NonEmpty(CrossJoin(
   [Event Received Date].[Month].[All].Children
  , [Event Received Date].[Week].children
  , [Event Received Date].[Date].Members
  )
 ) 
+
NonEmpty(CrossJoin(
   [Event Received Date].[Month].[All].Children
  , [Event Received Date].[Week].[All]
  , [Event Received Date].[Date].[All]
  )
 ) 

give what you want?

Frank

Answer 4

Thanks - that worked!