topic Re: Rank based on dates (top 5) in DAX Commands and Tips

Rank based on dates (top 5)

carrascojg — Fri, 22 Sep 2023 19:48:58 GMT

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

Re: Rank based on dates (top 5)

lbendlin — Sun, 24 Sep 2023 17:05:33 GMT

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

Re: Rank based on dates (top 5)

carrascojg — Wed, 27 Sep 2023 19:58:53 GMT

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!