Ranking in DirectQuery mode

[moizsherwani]

Hi Guys,

So here is my problem, below is a table with events related to a project at the given times. What I need is to create a RANK column (as shown below) based on event time per project. The table should illustrate this clearly, please remember I am in directquery mode.

Project - EVENTID  - TIME            - RANK

A          - 1234        - 1/1/2017     - 1

A          - 2345        - 1/7/2017     - 2 

A          - 3456        - 1/14/2017   - 3

B          - 7890         - 6/1/2017    - 1

B          - 8901         - 6/7/2017    - 2

B          - 9012          - 6/14/2017  - 3

C          - 6543          - 9/1/2017    -1

D          - 9876          - 8/1/2017   -1

D          - 7865          - 8/7/2017   -2

Any help here would be greatly appreciated.

Thanks,

Moiz

[Eric_Zhang · Accepted solution]

---

@moizsherwani wrote:

Hi Guys,

So here is my problem, below is a table with events related to a project at the given times. What I need is to create a RANK column (as shown below) based on event time per project. The table should illustrate this clearly, please remember I am in directquery mode.

Project - EVENTID  - TIME            - RANK

A          - 1234        - 1/1/2017     - 1

A          - 2345        - 1/7/2017     - 2 

A          - 3456        - 1/14/2017   - 3

B          - 7890         - 6/1/2017    - 1

B          - 8901         - 6/7/2017    - 2

B          - 9012          - 6/14/2017  - 3

C          - 6543          - 9/1/2017    -1

D          - 9876          - 8/1/2017   -1

D          - 7865          - 8/7/2017   -2

Any help here would be greatly appreciated.

Thanks,

Moiz

---

@moizsherwani

Try to create a measure as below. As to the "-1", it looks that there's some issue when sorting asc in RANKX, the rank starts from 2, instead of 1.

rank =
RANKX (
    ALLEXCEPT ( r, r[project] ),
    CALCULATE ( MIN ( r[time] ), ALLEXCEPT ( r, r[project], r[time] ) ),
    ,
    ASC,
    DENSE
) -1

[moizsherwani]

@Eric_Zhang O M G! that is the best solution ever. Can you please do me a massive favor and explain the logic here, I would be ever so grateful.