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

Calling all Data Engineers! Fabric Data Engineer (Exam DP-700) live sessions are back! Starting October 16th. Sign up.

Reply
Fusilier
Helper III
Helper III

Help with RANK function with and dynamic formatting

Hi,

Two parts to this question:

1. I have data that is based on responses where the respondent is presented with a list of options and selects either Yes or No depending on whether the option is important to them.

Data of a count of all the 'YES' responses is currently shown in a table:

response.png

I've been trying to add a ranking column to the table without much success.

The measure I've been trying to do this with is:

Rank- Important =
RANK(DENSE, ALLSELECTED('Combined Table'[Answer]))
 
Can sombody point out what I've done wrong with this measure please?
N.B. The table could be filtered by any number of different filters (e.g. age group or location) so the ranking would need to just be for the rows currently shown in the table 
 
2. I then want to be able to dynamically color the text of the top three ranked rows in a different color to the other rows.
I presume I would just be if the rank value was <=3
 
Hope somwbody can point me in the right direction?
 
 
1 ACCEPTED SOLUTION
SamWiseOwl
Super User
Super User

Hi @Fusilier 

Create this as a new measure, make sure to put your own table and column names in

Rank Questions =
RankX( --Performs an expression for each row
        AllSelected('Combined Table'[Question]) --Remove internal filters keeps external
        ,
Calculate(Count(Combined Table'[Answer])) --Calculate applies a fitler

        ,DESC --Sort most to least

        ,Skip--If 2 questions rank 1st, third question gets 3nd not 2nd
)

Apply this measure in Cell elements  click the Fx symbol and change the Format Style to Field value
Colour rank =
If( [Rank Questions] <= 3, "Green", "Red")

SamWiseOwl_1-1723474720572.png

 

SamWiseOwl_0-1723474600445.png

 


If you are happy with this answer please mark as a solution for others to find !

Kudos are always appreciated! Check out our free Power BI video courses.

View solution in original post

2 REPLIES 2
SamWiseOwl
Super User
Super User

Hi @Fusilier 

Create this as a new measure, make sure to put your own table and column names in

Rank Questions =
RankX( --Performs an expression for each row
        AllSelected('Combined Table'[Question]) --Remove internal filters keeps external
        ,
Calculate(Count(Combined Table'[Answer])) --Calculate applies a fitler

        ,DESC --Sort most to least

        ,Skip--If 2 questions rank 1st, third question gets 3nd not 2nd
)

Apply this measure in Cell elements  click the Fx symbol and change the Format Style to Field value
Colour rank =
If( [Rank Questions] <= 3, "Green", "Red")

SamWiseOwl_1-1723474720572.png

 

SamWiseOwl_0-1723474600445.png

 


If you are happy with this answer please mark as a solution for others to find !

Kudos are always appreciated! Check out our free Power BI video courses.

Thank you. Works great!

Helpful resources

Announcements
FabCon Global Hackathon Carousel

FabCon Global Hackathon

Join the Fabric FabCon Global Hackathon—running virtually through Nov 3. Open to all skill levels. $10,000 in prizes!

October Power BI Update Carousel

Power BI Monthly Update - October 2025

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

FabCon Atlanta 2026 carousel

FabCon Atlanta 2026

Join us at FabCon Atlanta, March 16-20, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM.