Filtering one of the columns in an MDX query

Question

I am comparatively new to MDX. I am working witht he following query :-

WITH
SET [Organisation Default Member] 
AS
STRTOMEMBER(iif(isempty(LadbrokesSAS.GetDimensionSecurityUserDefaultOrgMember(USERNAME)),"[Organisation].[Organisation Hierarchy].[ALL]",LadbrokesSAS.GetDimensionSecurityUserDefaultOrgMember(USERNAME)),CONSTRAINED)

MEMBER [Measures].[ParameterCaption] AS '[Organisation].[Organisation    Hierarchy].CURRENTMEMBER.MEMBER_CAPTION' 
MEMBER [Measures].[ParameterValue] AS '[Organisation].[Organisation    Hierarchy].CURRENTMEMBER.UNIQUENAME' 
MEMBER [Measures].[ParameterLevel] AS '[Organisation].[Organisation Hierarchy].CURRENTMEMBER.LEVEL.ORDINAL' 
MEMBER [Measures].[ParameterCaptionIndented] AS Space([Organisation].[Organisation Hierarchy].CURRENTMEMBER.LEVEL.ORDINAL) + [Organisation].[Organisation  Hierarchy].CURRENTMEMBER.MEMBER_CAPTION

SET [Organisation]
AS Descendants([Organisation Default Member] ,[Organisation].[Organisation Hierarchy].   [Key Organisation],SELF_AND_BEFORE)

SELECT 
{
 [Measures].[ParameterValue]
,[Measures].[ParameterCaptionIndented]
} ON COLUMNS , 
{Organisation}
 ON ROWS 
 FROM [ShopTradingCube]

The above query returns results like below:-

Now I want to filter the ParameterValue such that if it contains a value containing '[Organisation].[Organisation Hierarchy].[Supervisor - HO Manager]' it should not include that in the results. eg. [Organisation].[Organisation Hierarchy].[Supervisor - HO Manager].&[L7_Z_250_Closed]

I tried approaches using a where condition or by using an Except function. However, I always got some error no matter what all I tried. Can someone please let me know what should be my syntax and what is the most efficient way to achieve this?

Answer 1

Modify your set to the following:

SET [Organisation]
AS Descendants([Organisation Default Member] ,[Organisation].[Organisation Hierarchy].       [Key Organisation],SELF_AND_BEFORE)
   -
   Descendants([Organisation].[Organisation Hierarchy].[Supervisor - HO Manager])

This works like except.