Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago
Solved

RANK with PARTITION BY Like SQL

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-J...
Need Help
  • AlexisOlson's avatar
    AlexisOlson
    1 year ago

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

     

    Try this:

    RANK (
    DENSE,
    ORDERBY ( 'Table'[Date], ASC ),
    PARTITIONBY ( 'Table'[ID] )
)
  • FBergamaschi's avatar
    FBergamaschi
    1 year ago

    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

Anonymous's avatar
Anonymous
Not applicable
1 year ago

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's avatar
FBergamaschi
Super User
1 year ago

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