Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Ranking based on criteria

Hi guys, 

 

I am stuck on the problem and looking for some help. I want to rank the Class A as one group and other Classes B,C,D, etc as another. I also want to find the time difference between everytime class A ends and then next one begin. I.e 4hr between rank 1 and 2 and 3hr between rank 2 and 3 of class A.

 

index dateTime class item ranks

1 3/21/2021 9:00 A a.1 1

2 3/21/2021 10:00 A a.2 1

3 3/21/2021 11:00 A a.3 1

4 3/21/2021 12:00 B b.1 1 

5 3/21/2021 13:00 B b.2 1

6 3/21/2021 14:00 C c.1 1

7 3/21/2021 15:00 A a.1 2

8 3/21/2021 16:00 A a.2 2

9 3/21/2021 17:00 b b.1 2 

10 3/21/2021 18:00 c c.1 2

11 3/21/2021 19:00 A a.1 3

12 3/21/2021 20:00 A a.2 3

 

 

 

Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    Hi Anonymous ,

     

    Create calculated columns as below.

    _calss = IF('Table'[class]="A",'Table'[class],"Others")

_datetime = IF('Table'[_calss]<>CALCULATE(MAX('Table'[_calss]),FILTER('Table','Table'[index]=EARLIER('Table'[index])-1)),'Table'[dateTime])

timegroup = 
VAR LastNonBlankindex =
    CALCULATE (
        LASTNONBLANK ( 'Table'[index], 1 ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[index] <= EARLIER ( 'Table'[index] )
                && NOT ( ISBLANK ( 'Table'[_datetime] ) )
        )
    )
RETURN
    CALCULATE (
        SUM ( 'Table'[_datetime] ),
        FILTER ( ALL ( 'Table' ), 'Table'[index] = LastNonBlankindex )
    )

_rank = RANKX(FILTER('Table','Table'[_calss]=EARLIER('Table'[_calss])),'Table'[timegroup],,ASC,Dense)

     

    Best Regards,

    Jay

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hey guys, I was able to solve time difference part can anyone help me with rank part.

     

    Thanks

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi Anonymous ,

     

    Create calculated columns as below.

    _calss = IF('Table'[class]="A",'Table'[class],"Others")

_datetime = IF('Table'[_calss]<>CALCULATE(MAX('Table'[_calss]),FILTER('Table','Table'[index]=EARLIER('Table'[index])-1)),'Table'[dateTime])

timegroup = 
VAR LastNonBlankindex =
    CALCULATE (
        LASTNONBLANK ( 'Table'[index], 1 ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[index] <= EARLIER ( 'Table'[index] )
                && NOT ( ISBLANK ( 'Table'[_datetime] ) )
        )
    )
RETURN
    CALCULATE (
        SUM ( 'Table'[_datetime] ),
        FILTER ( ALL ( 'Table' ), 'Table'[index] = LastNonBlankindex )
    )

_rank = RANKX(FILTER('Table','Table'[_calss]=EARLIER('Table'[_calss])),'Table'[timegroup],,ASC,Dense)

     

    Best Regards,

    Jay