Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Jasleen123
New Member

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

Need to achieve below output:

ValuesAgreementsNotesRankOutput = concatenateOutput: Value
1Hi4Hi, How are you, I am fine, Thank you1
1How are you3  
1I am fine2  
1Thank you1  
2good3good,to,see u2
2to 2  
2see u1  
3all2all, ok3
3ok1  
4great1great4
5thanks1thanks5
6 REPLIES 6
techies
Super User
Super User

Hi @Jasleen123 created a calculated column for rank

 

RankColumn =
RANKX(
    FILTER(
        Sheet13,  
        Sheet13[Values] = EARLIER(Sheet13[Values])  
    ),
    Sheet13[AgreementsNotes],  
    ,
    DESC,  
    DENSE  
)
― Power BI | Microsoft Fabric | PL-300 | DP-600 | Blog: medium.com/@cseprs_54978

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

yes

― Power BI | Microsoft Fabric | PL-300 | DP-600 | Blog: medium.com/@cseprs_54978
mdaatifraza5556
Super User
Super User

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,
            ""
        )


Screenshot 2025-03-20 125441.png

 

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

Regards 
Md Aatif Raza

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

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
    )

Screenshot 2025-03-25 144721.png

 


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.

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.