Help With RANKX

UB40 — Mon, 07 Mar 2022 19:14:50 GMT

I'm very new to PowerBi and am looking for some help with the RANX function. I have a table, 'Result Grades', that needs to grouped as per the table below i.e 'RecNum', 'AcYr', 'Assessment', 'A8 Order' and then ranked by the 'Result Pts' (ascending). I have tried many different ways and the closest I've come to solving the problem is with the formula :-

RankA8 =

RANKX(
FILTER(ALL(
'Result Grades'),
'Result Grades'[RecNum] = EARLIER('Result Grades'[RecNum])&&[A8Order]="1"
),
'Result Grades'[RecNum]&[AcYr]&[Assessment]&[A8Order]&[ResultPts]&[Code]
,,desc
)

The only problem with this formula is it just ranks all the grades for each 'RecNum from 1 to the highest value. Is there any way to achieve the ranking as per the column 'Rank Wanted' as below.

RecNum	AcYr	Assessment	A8Order	Code	Result Pts	RankA8	Rank Wanted
8998	2017	3	1	aa	6	1	1
8998	2017	3	1	bb	5	2	2
8998	2017	2	1	bb	3	3	1
8998	2017	2	1	aa	3	4	2
8998	2017	1	1	bb	7	5	1
8998	2017	1	1	aa	7	6	2
10265	2021	1	1	bb	5	1	1
10265	2021	1	1	aa	5	2	2
10265	2020	3	1	bb	5	3	1
10265	2020	3	1	aa	4	4	2
10265	2020	2	1	bb	5	5	1
10265	2020	2	1	aa	4	6	2
10265	2020	1	1	bb	6	7	1
10265	2020	1	1	aa	5	8	2
10265	2019	2	1	bb	5	9	1
10265	2019	2	1	aa	5	10	2
10265	2018	3	1	bb	4	11	1
10265	2018	2	1	bb	3	12	1
10265	2018	1	1	En	3	13	1
10265	2017	3	1	En	3	14	1
10265	2017	2	1	En	4	15	1
10265	2017	1	1	En	4	16	1

Re: Help With RANKX

amitchandak — Tue, 08 Mar 2022 03:10:04 GMT

UB40 , A new Rank Column

RANKX(
FILTER('Result Grades',
'Result Grades'[RecNum] = EARLIER('Result Grades'[RecNum]) && 'Result Grades'[RecNum] = earlier('Result Grades'[RecNum]) &&
'Result Grades'[AcYr] = earlier('Result Grades'[AcYr]) && 'Result Grades'[Assessment] = earlier('Result Grades'[Assessment]) &&
[A8Order]="1"
),
[ResultPts]
,,desc
)

Re: Help With RANKX

UB40 — Tue, 08 Mar 2022 07:39:41 GMT

Thank you so much for helping. 🙂

topic Re: Help With RANKX in DAX Commands and Tips

Help With RANKX

Re: Help With RANKX

Re: Help With RANKX