MDX: Filtering repeated

Question

I would like to write an MDX query that only show values when a specific repeated dimesions are repeatid more tha one.

That is my mdx query:

SELECT 
NON EMPTY { [Measures].[Value] } ON COLUMNS,
NON EMPTY { ([Dim Result].[Sample Number].[Sample Number].ALLMEMBERS 
             * [Dim Parameter].[IdParameter].[IdParameter].ALLMEMBERS ) } ON ROWS                                 
FROM [Cube]

the result its like that:

Sample Number          IdParameter        Value
1                      3                 5
1                      4                 6
2                      3                 2
3                      4                 0

What i want is to get only repeated sample number values like this:

Sample Number          IdParameter       Value
1                      3                 5
1                      4                 6   

And remove the other values that are not repeated

Sample Number          IdParameter       Value
2                      3                 2
3                      4                 0

I don´t know how i can do this, it is posible?

I write the next mdx:

SELECT NON EMPTY {[Measures].[Recuento Fact Result]} ON COLUMNS,

 NON EMPTY { [Dim Result].[Sample Number].[Sample Number] }  ON ROWS
 FROM ( SELECT ({ [Dim Parameter].[IdParameter].&[420] , [Dim Parameter].[IdParameter].&[20] }    ) ON COLUMNS 

 FROM [cube])

the result is that:

Sample Number    Recount Fact Result 
1                          1
2                          2
3                          2
4                          1

What i want is get only the Sample number where the recount is biger than 1

thanks for your help

Answer 1

My approach would be to create a calculated member which would hold number of distinct non-empty members per SampleNumber. Then filter out those tuples where the above count is not greater than 1.

UNTESTED (not near my system)

WITH MEMBER [Measures].CntParametersPerSampleNumber as 
      DistinctCount(   
      NonEmpty(
               [Dim Parameter].[IdParameter].CHILDREN,
               ([Dim Result].[Sample Number].CURRENTMEMBER, [Measures].[Value])
              )
                   )

SELECT 
NON EMPTY { [Measures].[Value] } ON COLUMNS,
NON EMPTY { 
      Filter(
             (
             [Dim Result].[Sample Number].[Sample Number].ALLMEMBERS 
             * 
             [Dim Parameter].[IdParameter].[IdParameter].ALLMEMBERS 
             ), [Measures].CntParametersPerSampleNumber>1
            ) 
          } ON ROWS                                 
FROM [Cube]

Answer 2

You seem to have two separate questions.

The second question can be answered using the HAVING clause:

SELECT 
    NON EMPTY 
      {[Measures].[Recuento Fact Result]} ON 0,
    NON EMPTY 
      {[Dim Result].[Sample Number].[Sample Number]}  
    HAVING [Measures].[Recuento Fact Result] > 1
    ON 1
FROM 
  ( 
    SELECT 
      ({ 
        [Dim Parameter].[IdParameter].&[420] 
      , [Dim Parameter].[IdParameter].&[20] 
      }) ON 0
   FROM [cube]
  )

For you first question you should be able to use the Filter function iteratively to detect if sample number is repeated:

WITH
SET     [OrderedSampleNums] AS
        Order(
          [Dim Result].[Sample Number].[Sample Number].ALLMEMBERS ,
          [Dim Result].[Sample Number].CurrentMember.Caption,
          BASC
        )
SET     [RepeatedSampleNums] AS
        Filter(
          OrderedSampleNums ,
          OrderedSampleNums.Item(
            OrderedSampleNums.CurrentOrdinal-1
          ).Caption = [Dim Result].[Sample Number].CurrentMember.Caption
        )
SET     [NonRepeatedSampleNums] AS
        Except(
           OrderedSampleNums
          ,RepeatedSampleNums
        )
SELECT 
  NON EMPTY 
   {[Measures].[Value]} ON 0,
  NON EMPTY 
    [NonRepeatedSampleNums]
    * 
    [Dim Parameter].[IdParameter].[IdParameter].ALLMEMBERS
  ON 1                                 
FROM [Cube];

Not tested but I could try to prototype something against the AdvWorks cube to explore further?