Forum Discussion

smpa01's avatar
smpa01
Community Champion
5 years ago
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...
Need Help
  • CNENFRNL's avatar
    CNENFRNL
    5 years ago

    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.

CNENFRNL's avatar
CNENFRNL
Community Champion
5 years ago

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.

smpa01's avatar
smpa01
Community Champion
5 years ago

Many Thanks CNENFRNL for your time to clarify doubt.

Having said that, I will keep this thread open for a while as I intend to ask the same question to Marco and see if he can spare some time to look into it.