How to get only unique measures using MDX?

Question

I have an unconventional cube with a single dimension called ‘RowNumber’ and five measures called ‘Col0’, ‘Col1’ etc. Every coordinate of the cube is loaded with string values such as ‘Value14’ or 'Value3'.

In each measure there will be repeats of the same string value, so if I execute

SELECT [Measures].[Col2] ON 0, [RowNumber].[Row10]:[RowNumber].[Row14] ON 1 FROM [myCube]

I get these results:

        Col2
Row10   Value5
Row11   Value0
Row12   Value0
Row13   Value19
Row14   Value6

You can see in the table above that there are two Value0 entries. What I want to obtain is only the unique values from all of Col2, regardless of the RowNumber like this:

Col2 
Value5 
Value0 
Value19 
Value6

I appreciate this is an unconventional use of a cube, so maybe it's not possible. However, getting the unique values from one measure does not seem like it should be impossible, so I hope there is a way to do this.

Thanks in advance.

Can you change the cube? Can you build a dimension out of col2? — GregGalloway
You should definitely change your cube and add a dimension for Col2 as already sugested by GregGalloway — mxix

Danylo Korostil Danylo Korostil · Accepted Answer · 2017-01-09T21:25:58

MDX isn't really designed for this kind of things. But it's possible:

With
Set [OrderedRows] as 
Order(NonEmpty([RowNumber].[Row10]:[RowNumber].[Row14],[Measures].[Col2]),[Measures].[Col2])

Member [Measures].[Rank] as 
Rank([RowNumber].[RowNumber].CurrentMember, [OrderedRows])

Member [Measures].[Col2Unique] as 
IIF(
    ([OrderedRows].Item([Measures].[Rank] - 2),[Measures].[Col2]) = [Measures].[Col2],
    NULL,
    [Measures].[Col2]
)

Select {[Measures].[Col2],[Measures].[Col2Unique]} on 0,
Non Empty [OrderedRows] on 1
From [myCube]

How to get only unique measures using MDX?

1 Answers