MDX - Getting the sum instead of individual values

Question

My data looks like this:

ID                                   |PersonID  |CompanyID  |DateID  |Throughput |AmountType
33F467AC-F35B-4F24-A05B-FC35CF005981 |7         |53         |200802  |3          |0
04EE0FF0-511D-48F5-AA58-7600B3A69695 |18        |4          |201309  |5          |0
AB058AA5-6228-4E7C-9469-55827A5A34C3 |25        |69         |201108  |266        |0

with around a million rows. The columns names *ID refers to other tables, so they can be used as dimensions.

I have an OLAP cube with the column Throughput as Measure and the rest as dimensions.

I have a MDX query that is supposed to calculate Quartiles, other problem with the query is described and solved here: Trying to calculate quartiles in MDX.

The calculation works fine when I filter with one year, but when I filter with two years the result is the sum of both years. I demonstrate with an example. I have simplified the query to only show row counts because it still gives the same problem. The MDX query looks like this with one year:

WITH
SET selection as ({[Dates].[Year].&[2014]})
SET [NonEmptyIds] AS
 NonEmpty(
      [ThroughputID].[ID].[Id].ALLMEMBERS,
  {[Measures].[Throughput]} * [selection]
 )
 SET [ThroughputData] AS 
ORDER
    (    
        [NonEmptyIds],  
        [Measures].[Throughput], 
        BASC
     )
MEMBER [Measures].[RowCount] AS COUNT (ThroughputData)

SELECT
selection ON 0,
{[Measures].[RowCount]}
ON 1
FROM [Throughput]

The result from the above query is:

         |2014
RowCount |116 979

If I change the selection part to filter on 2015:

SET selection as ({[Dates].[Year].&[2015]})

I get this result:

         |2015
RowCount |68 038

Then, If I change the selection part to filter on both 2014 and 2015:

SET selection as ({[Dates].[Year].&[2014],[Dates].[Year].&[2015]})

I get this result:

         |2014    |2015
RowCount |185 017 |185 017

As 116 979 + 68 038 = 185 017, both years shows the sum of the individual years.

Does anyone know what I am doing wrong in the query?

Answer 1

SEBTHU's answer looks ok to me. I don't see a need to use the currentmember function within your custom measure.

Here is an equivalent script against the AdvWrks cube:

WITH 
  SET [YearSet] AS 
    {
      [Date].[Calendar Year].&[2007]
     ,[Date].[Calendar Year].&[2008]
    } 
  MEMBER [Measures].[RowCount] AS 
    Count
    (
      NonEmpty
      (
        [Customer].[Customer].[Customer]
       ,[Measures].[Internet Sales Amount]
      )
    ) 
SELECT 
  [Measures].[RowCount] ON 0
 ,[YearSet] ON 1
FROM [Adventure Works];

This is what the above returns i.e. not static:

You can also use a combination of the SUM function with IIF to construct a count measure like this - it can be fast in certain contexts:

WITH 
  SET [YearSet] AS 
    {
      [Date].[Calendar Year].&[2007]
     ,[Date].[Calendar Year].&[2008]
    } 
  MEMBER [Measures].[RowCount] AS 
    Count
    (
      NonEmpty
      (
        [Customer].[Customer].[Customer]
       ,[Measures].[Internet Sales Amount]
      )
    ) 
  MEMBER [Measures].[RowCountFAST] AS 
    Sum
    (
      [Customer].[Customer].[Customer]
     ,IIF
      (
         [Measures].[Internet Sales Amount] = 0
        ,null
        ,1
       )
    ) 
SELECT 
  {
    [Measures].[RowCount]
   ,[Measures].[RowCountFAST]
  } ON 0
 ,[YearSet] ON 1
FROM [Adventure Works];

Result of above:

This alternative approach applied to your scenario:

WITH 
  MEMBER [Measures].[CountNonEmptyThings] AS 
    Sum
    (
      [ThroughputID].[ID].[Id]
     ,IIF
      (
        [Measures].[Throughput] = 0
       ,NULL
       ,1
      )
    ) 
SELECT 
  [Measures].[CountNonEmptyThings] ON 0
 ,[Dates].[Years].MEMBERS ON 1
FROM [Throughput];