Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
moizsherwani
Continued Contributor
Continued Contributor

Ranking in DirectQuery mode

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

Thanks,

Moiz
Was I able to answer your question? Mark my post as a solution to help others. Kudos if you liked the solution.
1 ACCEPTED SOLUTION
Eric_Zhang
Employee
Employee


@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

Capture.PNG

View solution in original post

2 REPLIES 2
Eric_Zhang
Employee
Employee


@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

Capture.PNG

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

Thanks,

Moiz
Was I able to answer your question? Mark my post as a solution to help others. Kudos if you liked the solution.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.