Forum Discussion
RANKX returning unexpected tie
Community SupportAs 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
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.
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.