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]