Forcing RANKX to rank from 1

[smpa01]

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

| 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)

[CNENFRNL · Accepted solution]

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]

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]

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.