WITH
MEMBER [Measures].[Rank]ASRank([Product].[Model Name].CurrentMember,[Product].[Model Name].[Model Name],
[Measures].[ORDER Count]
)
SET [Sorted Models]AS
ORDER(
[Product].[Model Name].[Model Name]
,[Measures].[ORDER Count]
,BDESC)MEMBER [Measures].[Previous Model INDEX]AS(Rank(
[Product].[Model Name].CurrentMember,[Sorted Models]
)
-
2
)
MEMBER [Measures].[Dense Rank]AS
CaseWhen [Measures].[Rank] = 1
Then 1
Else(
[Sorted Models].Item([Measures].[Previous Model INDEX]),[Measures].[Dense Rank]
)
+
Iif(
(
[Sorted Models].Item([Measures].[Previous Model INDEX]),[Measures].[ORDER Count]
)
=
[Measures].[ORDER Count]
,0
,1
)
EndSELECT{
[Measures].[Rank],
[Measures].[Dense Rank]
} ON 0,{
[Sorted Models]
}
ON 1FROM [Adventure Works]
No comments:
Post a Comment