MDX Rank of Dimension unioned with itself

152 Views Asked by At

I am trying to list the row numbers for a dimension unioned with itself, however I continue to get duplicate row numbers instead of a unique one for each row. For example, I am trying to list the days of the week (Sunday - Saturday) twice such that I have rows 1-14. I'm getting repeating row numbers since i'm using the same dimension in my union (both instances of Monday = 1 instead of 1 and 8). How do I get unique row numbers?

Thanks in advance

WITH

MEMBER [Measures].[Row Number] AS 
    Rank( 
         StrToTuple( 
                    "( " +
                     Generate ( 
            Head ( Axis(0),
            Axis(1).Item(0).Count
            ) AS RN,
            "Axis(1).Item(0).Item(" +
            CStr(RN.CurrentOrdinal -1) +
            ").Hierarchy.CurrentMember",
            ","
            ) + ")"
            ),
Axis(1)
)


SET [AllMembers2] AS
UNION([Date].[Day Name].ALLmembers , [Date].[Day Name].ALLMembers, ALL)


select ([Measures].[Row Number]) on columns
,[AllMembers2] on rows

from [Adventure Works]
0

There are 0 best solutions below