filter dimension members and adjust 'All' member based on filter

Question

I have the following MDX statement which results in a list of customers per depot who have a nonempty value for jobs.cubic. This statement also filters out customers whose member_caption starts with 'PLU'.

If I include the 'All' member of the customer hierarchy it totals all the customers including the customers that have been filtered out by the 'PLU' filter.

Is it possible to select for the customers as I have in the statement and also return the 'All' member minus the values for the customers filtered out by the 'PLU' filter.

Ideally I would like to get the total ('All' customers) for each depot as the first row for each depot.

SELECT 
  {[Measures].[Job Cubic]} ON 0
 ,NonEmpty
  (
      {
        [Depot].[State - Depot].[214]
       ,[Depot].[State - Depot].[325]
       ,[Depot].[State - Depot].[447]
       ,[Depot].[State - Depot].[534]
       ,[Depot].[State - Depot].[611]
      }
    * 
      {
        {
          Filter
          (
            [Transport Customer].[Customer].[All Customers].Children
           ,
              Left
              (
                [Transport Customer].[Customer].CurrentMember.Properties('Member_Caption')
               ,3
              )
            <> 'PLU'
          )
        }
      }
   ,{[Measures].[Job Cubic]}
  ) ON 1
FROM [Transport KPIs]
WHERE 
  [Fiscal Date].[Year-Qtr-Month-Week-Date].[Month].&[Sep 16];

whytheq whytheq · Accepted Answer · 2016-10-27T21:16:25

First you could move the filtered set into a WITH clause - then create a set that is all customers except your filtered set - you then need to aggregate this set into a calculated member of the hierarchy [Transport Customer].[Customer].

WITH 
SET [FilteredSet] AS
   Filter(
      [Transport Customer].[Customer].[All Customers].Children
     ,Left(
        [Transport Customer].[Customer].CurrentMember.Member_Caption
       ,3
      )
      <> 'PLU'
   )
SET [All_except_FilteredSet] AS
   EXCEPT(
      [Transport Customer].[Customer].[All Customers].Children
     ,[FilteredSet]
   )
MEMBER [Transport Customer].[Customer].[All].[All_except_FilteredSet] AS
   AGGREGATE([All_except_FilteredSet])
SELECT 
  {[Measures].[Job Cubic]} ON 0
 ,NonEmpty
  (
      {
        [Depot].[State - Depot].[214]
       ,[Depot].[State - Depot].[325]
       ,[Depot].[State - Depot].[447]
       ,[Depot].[State - Depot].[534]
       ,[Depot].[State - Depot].[611]
      }
    * 
      {
        [Transport Customer].[Customer].[All].[All_except_FilteredSet],
        [FilteredSet]
      }
   ,[Measures].[Job Cubic]
  ) ON 1
FROM [Transport KPIs]
WHERE 
  [Fiscal Date].[Year-Qtr-Month-Week-Date].[Month].&[Sep 16];

filter dimension members and adjust 'All' member based on filter

1 Answers