topic Re: Using Rank function in Power BI want to achieve the below output in DAX Commands and Tips

Using Rank function in Power BI want to achieve the below output

Jasleen123 — Thu, 20 Mar 2025 06:43:24 GMT

Need to achieve below output:

Values	AgreementsNotes	Rank	Output = concatenate	Output: Value
1	Hi	4	Hi, How are you, I am fine, Thank you	1
1	How are you	3
1	I am fine	2
1	Thank you	1
2	good	3	good,to,see u	2
2	to	2
2	see u	1
3	all	2	all, ok	3
3	ok	1
4	great	1	great	4
5	thanks	1	thanks	5

Re: Using Rank function in Power BI want to achieve the below output

mdaatifraza5556 — Thu, 20 Mar 2025 07:26:10 GMT

Hi Jasleen123
Can you please try this

----------1st measure----------------------------

concat =

VAR CurrentValue = SELECTEDVALUE('Table'[Values])

VAR result =

CALCULATE(

CONCATENATEX(

'Table',

'Table'[AgreementsNotes],

", ",

'Table'[Rank],

DESC

ALLEXCEPT('Table', 'Table'[Values])

)

VAR MaxRank = CALCULATE(

MAX('Table'[Rank]),

ALLEXCEPT('Table', 'Table'[Values])

)

RETURN

IF(

SELECTEDVALUE('Table'[Rank]) = MaxRank,

result,

)

----------2nd measure----------------------------

output value =

VAR curv = SELECTEDVALUE('Table'[Values])

VAR MaxRank = CALCULATE(

MAX('Table'[Rank]),

ALLEXCEPT('Table', 'Table'[Values])

)

RETURN

IF(

SELECTEDVALUE('Table'[Rank]) = MaxRank,

curv,

)

If you got your answer then Please accept it as solution.

Regards
Md Aatif Raza

Re: Using Rank function in Power BI want to achieve the below output

Jasleen123 — Tue, 25 Mar 2025 08:40:54 GMT

Need to create the "Rank" column as well as it is not pre-defined in the table I used.

as you have mentioned in the below :
SELECTEDVALUE('Table'[Rank]) = MaxRank,

'Table'[Rank] = ??????

Please provide the measure for this Rank column as well.

Thanks,

Jasleen

Re: Using Rank function in Power BI want to achieve the below output

mdaatifraza5556 — Tue, 25 Mar 2025 09:20:07 GMT

Hi Jasleen123

To create rank col.

First Create index col---

Go to Power Query --> Add col --> index col (start from 1)

save and load it

Now create calculated col for rank using below DAX.

rank =

RANKX(

FILTER('Table', 'Table'[Values] = EARLIER('Table'[Values])),

'Table'[Index],,

DESC,

Dense

)

Do the rest of the steps as I posted before.

If this answers your questions, kindly accept it as a solution.
Give kudos for this if it is helpful.

Re: Using Rank function in Power BI want to achieve the below output

techies — Tue, 25 Mar 2025 10:27:54 GMT

Hi Jasleen123 created a calculated column for rank

RankColumn =

RANKX(

FILTER(

Sheet13,

Sheet13[Values] = EARLIER(Sheet13[Values])

Sheet13[AgreementsNotes],

DESC,

DENSE

)

Re: Using Rank function in Power BI want to achieve the below output

mdaatifraza5556 — Tue, 25 Mar 2025 10:42:10 GMT

Hi techies

This will give incorrect rank for values 2 (Ranking will be alphabetically so using Sheet13[AgreementsNotes] col will give incorrect ranking).

so index is required for the correct ranking

Re: Using Rank function in Power BI want to achieve the below output

techies — Tue, 25 Mar 2025 10:53:01 GMT

yes