Rank based on dates (top 5)

[carrascojg]

Hi all,

I have this table:

Country       Company CountCo CountB System date

Brazil            M             1             1          09/01/2023 
Brazil            P               2            2          09/01/2023 
Mexico         P               1            1          09/01/2023 
Australia       F               2            2          07/01/2023 
Germany      Z               1            1          07/01/2023 
New Zeland F               1             1          07/01/2023 
Indonesia     R              1             1          06/01/2023 
Japan           A              1             1          06/01/2023 

Country, Company,System date: columns

CountCo (count of company per country), CountB (count of code per country): measures

How can i do a rank in order to get only the first 5 rows of this table ? . I mean:

Country       Company CountCo CountB System date Rank

Brazil            M             1             1          09/01/2023   1
Brazil            P               2            2          09/01/2023   2
Mexico         P               1            1          09/01/2023   3
Australia       F               2            2          07/01/2023   4
Germany      Z               1            1          07/01/2023   5

I've tried with this code, but i got wrong outputs.

SUMMARIZE(table,

        ROLLUP( table[Country.], table[Company], table[System date])

        ,"@Count Company", COUNT(table[Company])

        ,"@Count Code", COUNT(table[Code])

        ,"@Rank2" , RANK(DENSE,ALL(table),ORDERBY(table[System date]))

        ,"@Rank3" , RANKX(table, count(table[System date]),,ASC,Dense)

)

Best

[carrascojg · Accepted solution]

Ibendlin,

Thank you for your suggestion.

I finally solved creating a table like this:

Table_aux =

SUMMARIZE(table,

        ROLLUP( table[Country.], table[Company.], table[System date.])

        ,"@Count Company", COUNT(table[Company.])

        ,"@Count Code", COUNT(table[Code.])

)

Then, i use Top 5 in [System date] filter and adding a relative date in another [System date] filter in Filter Section.

Best!

[lbendlin]

That is not something you can do in DAX.  Add an index column in Power Query, or further upstream.