Rank - Partition By Year & Cycle

[prakash11440278]

HI All,

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

Actual Data	Actual Data	Actual Data	Actual Data	Actual Data	Expected Output
year	cycle		type	rnr_sequence	Rank
2021	4		A	1	1
2021	4		B	12	2
2021	4		C	3	3
2021	4		D	4	4
2021	4		E	5	5
2021	4		F	6	6
2021	4		G	7	7
2021	4		H	8	8
2021	4		I	10	9
2021	4		J	11	10
2020	1		A	1	1
2020	1		B	4	2
2020	1		C	3	3
2020	1		D	2	4
2020	1		E	5	5

[v-janeyg-msft · Accepted solution]

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

[Anonymous]

Try This Measure

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

[amitchandak]

@prakash11440278 , Assume you need a column rank

rankx(filter(Table, [Year] =earlier([Year]) ), [type],,asc,dense)