CODEWITHSUNDEEP
mdxdax
Can Aslan
Can Aslan

Reputation: 53

DAX - RANK Function

I have a RANK code from WEBI and need to write similar one in DAX or MDX.Both can work. But DAX will be more useful.

=Rank([OrderCount];([Category1];[Category2];[Category3]))

I have tried below code in DAX but it is not exact what i need.

=RANK.EQ(table1[OrderCount];table1[OrderCount])

Can you help me out write similar?

Grouping by Count. This is extra.

Upvotes: 0

Views: 566

Answers (2)

Nick Chobotar
Nick Chobotar

Reputation: 1

Here is the DAX measure for your question: 

Rank =
IF (
    HASONEVALUE ( YourTableName[ProductName] ),
    RANKX (
        ALL ( YourTableName ),
        CALCULATE ( SUM ( YourTableName[Order Quantity] ) ),
        ,
        ,
        DENSE
    )
)

IF (HASONEVALUE ( YourTableName[ProductName] ).... part will eliminate your Grand Total from showing 1

Upvotes: 0

Danylo Korostil
Danylo Korostil

Reputation: 1484

I'm not very good at DAX, but MDX way is the following:

with
Dynamic Set OrderedSet as
Order(
    NonEmptyCrossJoin(
        [Dim Product].[Subcategory Name].[Subcategory Name].Members,
        [Dim Product].[Category Name].[Category Name].Members
        [Measures].[Order Quantity],
        2
    ),
    [Measures].[Order Quantity],
    BDESC
)

Member [Measures].[Rank] as
Rank(

        ([Dim Product].[Subcategory Name].Currentmember, 
        [Dim Product].[Category Name].Currentmember),
        OrderedSet
)

select 
{[Measures].[Order Quantity],[Measures].[Rank]} on 0,
non empty OrderedSet on 1
from 
[Adventure Works DW2016CTP3]

DenseRank:

with
Dynamic Set OrderedSet as
Order(
    NonEmptyCrossJoin(
      [Dim Product].[Subcategory Name].[Subcategory Name].Members,
      [Dim Product].[Category Name].[Category Name].Members,
      [Measures].[Order Quantity],
       2
    ),
    [Measures].[Order Quantity],
    BDESC
)

Dynamic Set DenseOrderedSet as
Order(
    NonEmpty(
       OrderedSet,
       [Measures].[RankFirstMatch]
    ),
    [Measures].[Order Quantity],
    BDESC
)

Member [Measures].[Rank] as
Rank(
  ([Dim Product].[Subcategory Name].Currentmember,[Dim Product].[Category Name].CurrentMember),
  OrderedSet
)

Member [Measures].[RankFirstMatch] as
IIF(
    [Measures].[Order Quantity] 
    =
    (
        OrderedSet.Item([Measures].[Rank] -2),
        [Measures].[Order Quantity]
    ),
    NULL,
    [Measures].[Rank]
)

Member [Measures].[RankDenseSet] as
Rank(
  ([Dim Product].[Subcategory Name].Currentmember,[Dim Product].[Category Name].CurrentMember),
  DenseOrderedSet
)


Member [Measures].[DenseRank] as
IIF(
    [Measures].[RankDenseSet] = 0,
    (OrderedSet.Item([Measures].[Rank] -2),[Measures].[DenseRank]),
    [Measures].[RankDenseSet]
)


select {[Measures].[Order Quantity],[Measures].[Rank],[Measures].[RankFirstMatch],[Measures].[RankDenseSet],[Measures].[DenseRank]} on 0,
non empty OrderedSet on 1
from [Adventure Works DW2016CTP3]

Upvotes: 1

Related Questions