Issue while counting members in result set

Question

I am into an interesting issue. I have a dimension called Product with hierarchy Product as Rolodex1->Rolodex2->Rolodex3->Product ID.

I am trying to create a calculated measure [Product Count] to count the number of "Product ID" members (lowest level) under selection.

My measure looks like:

MEMBER [Measures].[Product Count] AS                                         
    COUNT(
      FILTER( 
         EXISTING [Product].[Product].[Product ID].MEMBERS
       , NOT ISEMPTY( [Measures].[Distinct Product Count] ) ))
)

[Distinct Product Count] is distinct count of Product ID. It should be 1 for existing Product ID's in selection.

The measure works when I select 2 members from same level and combine together and put in where condition. For eg it works on following query:

WITH
MEMBER [Product].[Product].[All Products].[Aggregation] AS 
  ' AGGREGATE( 
       { [Product].[Product].[Rolodex1].&[44], 
         [Product].[Product].[Rolodex1].&[45] }
   ) ' 
 SELECT 
   { [Measures].[Product Count] } ON COLUMNS ,   
   { [Paid Date].[Paid Date].[Paid Quarters].&[2016 - Q2]
    ,[Paid Date].[Paid Date].[Paid Quarters].&[2016 - Q3]  } ON ROWS     
 FROM [MyCube]   
 WHERE ( [Product].[Product].[All Products].[Aggregation] )

But it doesn't work with the following query when 2 members from different levels are selected and combined together:

WITH       
MEMBER [Product].[Product].[All Products].[Aggregation1] AS 
    ' AGGREGATE( 
        GENERATE( 
           FILTER( 
             { [Product].[Product].[Rolodex1].&[44], 
               [Product].[Product].[Rolodex2].&[4505] }
           , NOT ISEMPTY( 
               [Product].[Product].CURRENTMEMBER 
             )
           ), 
         HEAD( 
           HIERARCHIZE( 
             INTERSECT( 
              { [Product].[Product].[Rolodex1].&[44]
              , [Product].[Product].[Rolodex2].&[4505] } 
             ,ASCENDANTS( [Product].[Product].CURRENTMEMBER )
             )
            )
          )
         )
       ) '  
SELECT 
   { [Measures].[Product Count] } ON COLUMNS ,   
   { 
      [Paid Date].[Paid Date].[Paid Quarters].&[2016 - Q2]
     ,[Paid Date].[Paid Date].[Paid Quarters].&[2016 - Q3]  } ON ROWS     
FROM [Cost and Utilization]    
WHERE ( [Product].[Product].[All Products].[Aggregation1] )

The MDX for calculated members generated here [Product].[Product].[All Products].[Aggregation1] is the query generated by Proclarity when I select 2 members from different levels and combine together.

Please help. Has anybody encountered this issue before?

Thank you in advance.

whytheq whytheq · Accepted Answer · 2016-10-22T07:25:29

Initial advice is to use the following more efficient pattern for your measure:

MEMBER [Measures].[Product Count] AS                                         
    SUM(
       EXISTING [Product].[Product].[Product ID].MEMBERS
       IIF (
         NOT ISEMPTY( [Measures].[Distinct Product Count] )
         ,1
         ,NULL
        )
     )

An alternative to the Proclarity script might be the following - does this return the result you expect?

WITH       
MEMBER [Product].[Product].[All Products].[Aggregation1] AS 
    ' AGGREGATE(
        EXISTS( 
          [Product].[Product].[Product ID].MEMBERS
         ,{ [Product].[Product].[Rolodex1].&[44]
          , [Product].[Product].[Rolodex2].&[4505] } 
         )
       ) '  
SELECT 
   { [Measures].[Product Count] } ON COLUMNS ,   
   { 
      [Paid Date].[Paid Date].[Paid Quarters].&[2016 - Q2]
     ,[Paid Date].[Paid Date].[Paid Quarters].&[2016 - Q3]  } ON ROWS     
FROM [Cost and Utilization]    
WHERE ( [Product].[Product].[All Products].[Aggregation1] )

Issue while counting members in result set

2 Answers