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

dateemp_id
1/1/20212
1/2/20212
1/3/20212
1/4/20212
1/1/20213
1/2/20213
1/3/20213
1/4/20213
1/1/20216
1/2/20216
1/3/20216
1/4/20216
2/1/20212
2/2/20212
2/3/20212
2/4/20212
2/1/20213
2/2/20213
2/3/20213
2/4/20213
2/1/20216
2/2/20216
2/3/20216
2/4/20216

 

| 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      |
| 1/3/2021 | 3      |
| 1/4/2021 | 3      |
| 1/1/2021 | 6      |
| 1/2/2021 | 6      |
| 1/3/2021 | 6      |
| 1/4/2021 | 6      |
| 2/1/2021 | 2      |
| 2/2/2021 | 2      |
| 2/3/2021 | 2      |
| 2/4/2021 | 2      |
| 2/1/2021 | 3      |
| 2/2/2021 | 3      |
| 2/3/2021 | 3      |
| 2/4/2021 | 3      |
| 2/1/2021 | 6      |
| 2/2/2021 | 6      |
| 2/3/2021 | 6      |
| 2/4/2021 | 6      |

 

and my desired outcome is following which is ranking of date by employee

 

| date     | emp_id | rank |
|----------|--------|------|
| 1/1/2021 | 2      | 1    |
| 1/2/2021 | 2      | 2    |
| 1/3/2021 | 2      | 3    |
| 1/4/2021 | 2      | 4    |
| 1/1/2021 | 3      | 1    |
| 1/2/2021 | 3      | 2    |
| 1/3/2021 | 3      | 3    |
| 1/4/2021 | 3      | 4    |
| 1/1/2021 | 6      | 1    |
| 1/2/2021 | 6      | 2    |
| 1/3/2021 | 6      | 3    |
| 1/4/2021 | 6      | 4    |
| 2/1/2021 | 2      | 5    |
| 2/2/2021 | 2      | 6    |
| 2/3/2021 | 2      | 7    |
| 2/4/2021 | 2      | 8    |
| 2/1/2021 | 3      | 5    |
| 2/2/2021 | 3      | 6    |
| 2/3/2021 | 3      | 7    |
| 2/4/2021 | 3      | 8    |
| 2/1/2021 | 6      | 5    |
| 2/2/2021 | 6      | 6    |
| 2/3/2021 | 6      | 7    |
| 2/4/2021 | 6      | 8    |

 

To come to this I can use the following measure using ALLSELECTED

 

Measure 6 = 
VAR _1 = MAX('fact'[emp_id])
VAR _2 = RANKX(FILTER(ALLSELECTED('fact'),'fact'[emp_id]=_1),CALCULATE(MAX('fact'[date])),,ASC,Dense) RETURN _2

 

but I don't want to use ALLSELECTED as it has severe performance issue. Instead, I can use ALLEXCEPT to come here but the ranking with ALLEXCEPT does not start with 1. I wonder why? OwenAuger 

Secondly, how can I make changes in ALLEXCEPT measure to force the measure to start ranking from 1

 

Measure 2 = RANKX(ALLEXCEPT('fact','fact'[emp_id]),CALCULATE(max('fact'[date])),,ASC,Dense)

 

 

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.

3 Replies

  • 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.

  • smpa01's avatar
    smpa01
    Community Champion
    5 years ago

    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.