cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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

 A 151 5 B 235 6 C 123 4 D 0 1 E 12 2 F 55 3

Actual Result

Program Number Rank

 A 151 5 B 235 6 C 123 4 D 0 1 E 12 1 F 55 3

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
Super User

hi @Moonc

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

Frequent Visitor

@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

Super User

@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

Frequent Visitor

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

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

#### Fabric Community Update - September 2024

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

Top Solution Authors
Top Kudoed Authors