Forum Discussion

prakash11440278's avatar
prakash11440278
Icon for Post Prodigy rankPost Prodigy
4 years ago
Solved

Rank - Partition By Year & Cycle

HI All,

How to acheive the below desired output using DAX. Please help.

 

Actual DataActual DataActual DataActual DataActual DataExpected Output
yearcycle typernr_sequenceRank
20214 A11
20214 B122
20214 C33
20214 D44
20214 E55
20214 F66
20214 G77
20214 H88
20214 I109
20214 J1110
20201 A11
20201 B42
20201 C33
20201 D24
20201 E55
Need Help
  • v-janeyg-msft's avatar
    v-janeyg-msft
    4 years ago

    Hi, prakash11440278 

     

    According to your needs, I think you can create a column.

    Like this:

    Rank =
RANKX (
    FILTER (
        'Table',
        [year] = EARLIER ( 'Table'[year] )
            && [cycle] = EARLIER ( 'Table'[cycle] )
    ),
    [type],
    ,
    ASC
)

     

    Did I answer your question? Please mark my reply as solution. Thank you very much.
    If not, please feel free to ask me.

     

    Best Regards,

    Community Support Team _Janey

     

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Try This Measure

     

     

    Rank = 
RANKX (
    FILTER (
        ALL ('Table'[YEAR],'Table'[type] ),
       'Table'[YEAR] = MAX ( 'Table'[YEAR] )
    ),
    CALCULATE (MAX( ( 'Table'[type] ) )
),,ASC)

     

     

  • v-janeyg-msft's avatar
    v-janeyg-msft
    Icon for Community Support rankCommunity Support
    4 years ago

    Hi, prakash11440278 

     

    According to your needs, I think you can create a column.

    Like this:

    Rank =
RANKX (
    FILTER (
        'Table',
        [year] = EARLIER ( 'Table'[year] )
            && [cycle] = EARLIER ( 'Table'[cycle] )
    ),
    [type],
    ,
    ASC
)

     

    Did I answer your question? Please mark my reply as solution. Thank you very much.
    If not, please feel free to ask me.

     

    Best Regards,

    Community Support Team _Janey