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

[Jasleen123]

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

[techies]

Hi @Jasleen123 created a calculated column for rank

RankColumn =

RANKX(

    FILTER(

        Sheet13,  

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

    ),

    Sheet13[AgreementsNotes],  

    ,

    DESC,  

    DENSE  

)

[mdaatifraza5556]

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

[techies]

yes

[mdaatifraza5556]

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

[Jasleen123]

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

[mdaatifraza5556]

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.