MDX Measure greater than 1000

Question

Morning,

I have the following MDX and would like to place a filter on the measure 'Estimated Unbilled Outstanding Subtotal £'. I have searched through the forums and understand that I should be able to use FILTER or WHERE, but I cannot adapt the examples to work for me. Would really appreciate any assistance.

    SELECT 
    { [Measures].[Estimated Unbilled Outstanding Subtotal £] } ON COLUMNS             
   ,{ NONEMPTY( 
         { [Customer].[Billing Team].[All].CHILDREN } 
       * { [Customer].[Customer Name].[All].CHILDREN } 
       * { [Account].[Account ID].[All].CHILDREN } 
       * { [Account].[Account Type].&[Import]
         , [Account].[Account Type].&[Other Non Billable] } 
      , { [Measures].[Estimated Unbilled Outstanding Subtotal £] } 
      ) } ON ROWS  
    FROM [BDW];

Many thanks

Adrian

Answer 1

You need to put a filter around the ROWS clause:

SELECT 
{ [Measures].[Estimated Unbilled Outstanding Subtotal £] } ON COLUMNS             
,  
FILTER(
NONEMPTY( 
     { [Customer].[Billing Team].[All].CHILDREN } 
   * { [Customer].[Customer Name].[All].CHILDREN } 
   * { [Account].[Account ID].[All].CHILDREN } 
   * { [Account].[Account Type].&[Import]
     , [Account].[Account Type].&[Other Non Billable] } 
  , { [Measures].[Estimated Unbilled Outstanding Subtotal £] } 
  )
,[Measures].[Estimated Unbilled Outstanding Subtotal £] >1000
)
ON ROWS  
FROM [BDW];