RANK with PARTITION BY Like SQL

[Jamesfindog]

Hi,

I am looking to use a DAX formula, similar to the RANK with Partition By window function within SQL.

My desired result is the below:

ID	Date	Rank
1	01-Jan-21	1
1	01-Jan-21	1
1	02-Jan-21	2
1	03-Jan-21	3
2	01-Jan-21	1
2	02-Jan-21	2
3	01-Jan-00	1
4	01-Jan-00	1

This DAX is not working, I am receiving all 1 values as below:

Ranker =

RANKX(

    ALL(Table[ID]), Table[Date])

ID	Date	Rank
1	01-Jan-21	1
1	01-Jan-21	1
1	02-Jan-21	1
1	03-Jan-21	1
2	01-Jan-21	1
2	02-Jan-21	1
3	01-Jan-00	1
4	01-Jan-00	1

 Thanks for your help.

[AlexisOlson · Accepted solution]

The newer RANK function should make this easier than it used to be.

Try this:

RANK (
    DENSE,
    ORDERBY ( 'Table'[Date], ASC ),
    PARTITIONBY ( 'Table'[ID] )
)

[AlexisOlson · Accepted solution]

The newer RANK function should make this easier than it used to be.

Try this:

RANK (
    DENSE,
    ORDERBY ( 'Table'[Date], ASC ),
    PARTITIONBY ( 'Table'[ID] )
)

[Jamesfindog]

Thanks mate, perfect.

[MFelix]

Hi @Jamesfindog ,

Check this post.

https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/DAX-RANKX-with-Partition-By/m-p/2389...

[Jamesfindog]

Same issue, utilised exactly, still receiving all 1 values.

rn =

VAR CurrentDate =

    SELECTEDVALUE ( Table[Date] )

VAR RankingTable =

    CALCULATETABLE (

        SUMMARIZE ( Table, Table[Date] ),

        ALLSELECTED (), -- filter context of visual

        VALUES ( Table[ID]) -- retain current Category filter

    )

RETURN

    RANKX (

        RankingTable,

        Table[Date],

        CurrentDate,

        ASC

    )

[Jamesfindog]

Apologies, I desire this to be ranked by earliest date.

[FBergamaschi]

I do not understand the following

1  are yo ulooking for a column to add to the table or to a measure?

2 your desred result is confusing

ID	Date	Rank
1	01-Jan-21	1
1	01-Jan-21	1
1	02-Jan-21	2
1	03-Jan-21	3
2	01-Jan-21	1
2	02-Jan-21	2
3	01-Jan-00	1
4	01-Jan-00	1

do you want the rank within a year?

Thanks

If this helped, please consider giving kudos and mark as a solution

@me in replies or I'll lose your thread

consider voting this Power BI idea

Francesco Bergamaschi

MBA, M.Eng, M.Econ, Professor of BI

[Jamesfindog]

Hi mate,

A new column, which ranks the dates grouped by each ID.

The actual date value is irrelevant, I just want the earliest to be 1 for each ID, and the second 2, and so forth.

[FBergamaschi · Accepted solution]

Calculate column on Table, where you add a column to calculate the year

Year = YEAR ( Tabella[Date] )

Finale Column

RANK ( DENSE, ORDERBY( Tabella[Date] ), DEFAULT, PARTITIONBY( Tabella[YEAR]))

If this helped, please consider giving kudos and mark as a solution

@me in replies or I'll lose your thread

consider voting this Power BI idea

Francesco Bergamaschi

MBA, M.Eng, M.Econ, Professor of BI