Forum Discussion
Post PatronRankx-Ranking should begin when there is a value (not blank) on Ascending rank
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.
here is an example of the result:
thanks!
electrobrit try this measure
Rank = RANKX( FILTER( ALL( Store[StoreKey] ), [Avg Duration] <> BLANK() ), [Avg Duration], , ASC ) * DIVIDE( [Avg Duration], [Avg Duration] )
6 Replies
- parry2k
Super User
electrobrit try this measure
Rank = RANKX( FILTER( ALL( Store[StoreKey] ), [Avg Duration] <> BLANK() ), [Avg Duration], , ASC ) * DIVIDE( [Avg Duration], [Avg Duration] )
electrobritTHANK YOU! That worked
- 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?