Rankx-Ranking should begin when there is a value (not blank) on Ascending rank

[electrobrit]

RankX is confusing to me so hoping someone can help me through this.

I have stores that have an average duration of a task. The lower the better (so I have to sort asc), but if there is no "average duration" for the store their ranking should be 0, right now it's ranking them all 1 and the first one with average duration a ranking after all the 1s. (so if there is 30 1s and all 0 average duration, then the first one with a value is ranked 31).
I tried to filter out those with average duration is blank but the ranking does not change.

RankAvgDuration Per Store = rankx(ALL(VW_Store[storekey), [Average duration], ,ASC)
here is an example of the result:

Can someone help me revise the rankx formula to have a ranking of 0 (or blank) if no average duration is listed?
thanks!

[parry2k · Accepted solution]

@electrobrit try this measure

Rank = 
RANKX( 
FILTER( ALL( Store[StoreKey] ), 
[Avg Duration] <> BLANK()  
), 
[Avg Duration], , ASC ) * 
DIVIDE( [Avg Duration], [Avg Duration] ) 

[electrobrit]

Have another question, I used this same DAX when trying to get a little more granular with the actual employees. it helped with the same issue (thanks!), rank started with the first person who had actually had a value. However, the avg duration is the same for a lot of people creating duplicate ranks. How do I prevent this?


(I think it will help when I filter on the store too, no duplicates)

[parry2k]

@electrobrit are you trying to rank employees across all the stores or rank the employee within the store?

[electrobrit]

actually the requirement is both

[parry2k]

@electrobrit rank employees across all the dataset should be the same as we did for Rank for stores but yes ranking of employees withing store will need another enhancement to existing measure which I will post soon but in the meantime employee rank you should be able to get going.

[electrobrit]

THANK YOU! That worked