Removing blanks from rankings

[drivas771994]

Hi,

I'm having trouble with my rankx formula in the third column from the right. It is counting blanks as 1 rank and I'd like the formula to ignore them. I'd like it to be like the ranking on the right. Below is the formula I'm using.

Team Rank Bridge Timed Tests = CALCULATE( RANKX(all(DEMOGRAPHICS2[Athlete Name]),TESTS[Test Value Copy],,ASC,Dense),ALLSELECTED(DEMOGRAPHICS2[Team]),ALLSELECTED(TESTS[Test Name]),ALLSELECTED(TESTS[test_value]))

[EraDon]

Hi,

• If expression or value evaluates to BLANK it is treated as a 0 (zero) for all expressions that result in a number, or as an empty text for all text expressions.

ALL function remove all incoming filters.

So, Row context works on all [Athletes Name] as no filter.

as you asked tie with dense. maybe the first 6 athletes names are tied together (They are all considered 0 or null for Test_vale). IT will start rank 2 for the 7th one.

try to wrap ALL function with the FILTER function and remove null values such as
RANKX(FILTER(ALL(TESTS), TESTS[Test Value Copy] >0),
SUMX(<your calculation>),ASC,Dense),

ALLSELECTED(DEMOGRAPHICS2[Team]),ALLSELECTED(TESTS[Test Name]),ALLSELECTED(TESTS[test_value]))

I believe DEMOGRAPHICS2 is the Dim table and TESTS is the fact table.

hope this will help you to figure out where it went wrong.

Regards

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.