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

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
    )