Forum Discussion

DataVitalizer's avatar
DataVitalizer
Super User
2 years ago

RANKX missing values or redendant

Hi Community,

I have a table called "MyTable" with 3 columns (Category, SubCategory, Date)

I added a column using this DAX code to rank subcategories within each group by date:

MyRanking= 
RANKX(FILTER(ALL(MyTable),
MyTable[Category] = EARLIER(MyTable[Category])
),
MyTable[Date],,,Dense)

Here are the faces issues :
1. for some categories I get the same ranking when subcategories have the same date
2. for some categories some ranking numbers are missing.

Any suggestions?
Thank you in advance

Need Help

2 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    2 years ago

    DataVitalizer , for column try like

     

    MyRanking=
    RANKX(FILTER((MyTable),
    MyTable[Category] = EARLIER(MyTable[Category])
    MyTable[SubCategory] = EARLIER(MyTable[SubCategory])
    ),
    MyTable[Date],,asc,Dense)

     

    For measure you can use new rank or rownumber

    rank(DENSE,ALLSELECTED(MyTable[Category],MyTable[SubCategory],MyTable[Date]),ORDERBY([Date],asc))

     

    add partitionBy if needed