RANKX returning unexpected tie

[jmoskow]

UPDATE: So it turns out that something about the way I'm filtering data within RANKX is causing the problem, so I'll post the full dax. Essentially, I'm ranking local markets, regardless of which broader area markets may have been selected by the user, and excluding local markets that have blank measure values from being ranked. This successfully 1) hides local markets with blank measures and 2) prevents the blank market from being assigned a rank. FWIW, If I remove the FILTER and the CALCULATE within it, the value after zero has a different ranking, but blanks are assigned a rank. So it appears something about my filter or calculate formula is causing this. 

=IF(NOT(ISBLANK([Variance to Goal])),

    RANKX (FILTER(ALL('areas_and_local_markets'[Local Market]),CALCULATE([Variance to Goal],
    all(areas_and_local_markets[Area Name]))<>BLANK()),CALCULATE([Variance to Goal],
   all(areas_and_local_markets[Area Name])),,0),

    BLANK())

I have a measure that ranks values produced by a separate measure, excluding blanks. This works as expected, with one exception: If there is a zero included in the list of values being ranked, the value after the zero will always have the same rank as 

the zero, even though they're clearly different values (see screenshot). If you reverse the sort order, there is then a tie on the value before zero, even though, again, they are clearly separate values.

Thanks in advance for any help offered. 

 

[v-juanli-msft]

Hi @jmoskow

As said, you want a measure that ranks values produced by a separate measure, excluding blanks and zero. Does results below meet your requirement?

I just modify your measure as below to achieve it.

Measure2 = IF(NOT(ISBLANK([Measure1])),RANKX(ALL(Table1),[Measure1],,DESC),BLANK())

Best Regards

Maggie

[jmoskow]

My apologies, I'd omitted the reference to the measure (and desc) being ranked in my dax, which I've updated in my initial question. I pretty much already had what you suggested.

[jmoskow]

I have a measure that ranks values produced by a separate measure, excluding blanks. This works as expected, with one exception: If there is a zero included in the list of values being ranked, the value after the zero will always have the same rank as 

the zero, even though they're clearly different values (see below screenshot). If you reverse the sort order, there is then a tie on the value before zero, even though, again, they are clearly separate values.

The DAX is a little tricky because, essentially, I'm ranking local markets, regardless of which broader area markets may have been selected by the user, while also preventing local markets that have blank measure values from being ranked. This successfully 1) hides local markets with blank measures and 2) prevents the blank markets from being assigned a rank. FWIW, If I remove the FILTER and the CALCULATE within the below DAX, the value after zero has a (correct) different ranking, but blanks are assigned a rank, which I don't want. So it appears something about my filter or calculate formula is causing this. 

=IF(NOT(ISBLANK([Variance to Goal])),

    RANKX (FILTER(ALL('areas_and_local_markets'[Local Market]),CALCULATE([Variance to Goal],
    all(areas_and_local_markets[Area Name]))<>BLANK()),CALCULATE([Variance to Goal],
   all(areas_and_local_markets[Area Name])),,0),

    BLANK())

Thanks in advance for any help offered.