Forum Discussion
Solved
Forcing RANKX to rank from 1
Hi, My source data is following which is a minimum reproducible example of my large dataset date emp_id 1/1/2021 2 1/2/2021 2 1/3/2021 2 1/4/2021 2 1/1/2021 3 1/2/2021 3...
RANKX is one of the most underrated functions regarding its complexity. In addition, ALLEXCEPT even gets the case worse.
Measure 2 = RANKX(ALLEXCEPT('fact','fact'[emp_id]),CALCULATE(max('fact'[date])),,ASC,Dense)First, I hope you are clear on that ALLEXCEPT here is a table function; it constructs such a table to be consumed by CALCULATE(max('fact'[date])) for a lookup table for ranking in each row of the viz.
Secondly, CALCULATE(max('fact'[date])) for the lookup table evaluates under a mixed evaluation context, that's to say, the above table + current filter context.
Thirdly, CALCULATE(max('fact'[date])) is evaluated once again under current filter context only; and the result is used along with lookup table for ranking.
I can resolve this by using
Measure 8 = RANKX(FILTER(ALL('fact'),'fact'[emp_id]=MAX('fact'[emp_id])),CALCULATE(max('fact'[date])),,ASC,Dense)but I still wonder why RANKING did not start with 1 while using ALLEXCEPT.