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
Moonc
Frequent Visitor

RANKX Issues with 0s

I want my rank to start at 0 and go from there. Currently it is ranking my 0 values and the next lowest value as 1 and then ranking

=RANKX(FILTER(ALLSELECTED( [Program]), [NUMBER] ), [NUMBER],,ASC)

[Number] is a measure that is summing a value with some other conditions present

Expected Result

Program Number Rank

A1515
B2356
C1234
D01
E122
F553

Actual Result

Program Number Rank

A1515
B2356
C1234
D01
E121
F553

0 and the next lowest value are both getting ranked 1, which will not work for what I am trying to do.

 

I tried putting SUMX() around [NUMBER] in the ALLSELECTED, while that does work when the data is on its own, it breaks when I have to put the entire formula together (Two separate ranks for a matrix using ISINSCOPE() to change which rank is being applied)

 

I also tried setting the rank of 0 to 1 and then ranking +1. While this works for instances that there is one 0 present, if there are multiple 0s, I need the rank to skip. So both the 0s would be ranked 1 and then the next lowest value would be ranked 2, when I need it to be rank 3.

 

Any idea? I feel like I am not understanding something as the rank should just be applied to how the values are presented...

4 REPLIES 4
FreemanZ
Super User
Super User

hi @Moonc 

how about:

=RANKX(ALLSELECTED( [Program]), [NUMBER],,ASC)

@FreemanZ That works for when I am not working in a matrix. I should have made this more clear. I have a matrix with both program and subprogram, and I need to have a rank for each. So the formula that I am actullay using looks like the below

 

RANK = 

VAR _program = RANKX(ALLSELECTED( [Program]), [NUMBER],,ASC)

VAR _subProgram = RANKX(ALLSELECTED( [subProgram]), [NUMBER],,ASC)

 

RETURN

if(isinscope(program),_program,if(isinscope(subProgram),_subProgram))

 

This returns a proper rank for the program, but the subProgram has all the same rank

@Moonc , if both are on row and program is first and sub program is second then try like

 

RANK =

VAR _program = RANKX(ALLSELECTED( [Program]), [NUMBER],,ASC)

VAR _subProgram = RANKX(ALLSELECTED( [subProgram]), [NUMBER],,ASC)

Switch (True()

isinscope(subProgram,_subProgram,

isinscope(program),_program

)

 

The first one or the parent is always isinscope for the child 

 

IsInScope - Switch Rank at different levels: https://youtu.be/kh0gezKICEM

Share with Power BI Enthusiasts: Full Power BI Video (20 Hours) YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

@amitchandak This does not work, I am seeing the program ranks correct, but the subprogram ranks are incorrect. I don't see any pattern in the rank that would help me understand.

Helpful resources

Announcements
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.

June 2025 community update carousel

Fabric Community Update - June 2025

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