Forum Discussion

ruxandraalina's avatar
ruxandraalina
Icon for Helper I rankHelper I
4 years ago

Rank categories with duplicate categories

Hello,

 

I am trying to rank the column Group Category by Start Date for each Incident Number. I've used the RANKX formula, but I don't get the result I need.

I need my rank to look like below:

Any help is appreciated!

Thank you!

3 Replies

  • tamerj1's avatar
    tamerj1
    Icon for Community Champion rankCommunity Champion
    4 years ago

    Hi ruxandraalina 
    I don't think there is a simple solution for this. The problem is that you want the count to increase by 1 everytime the group category changes which is not  a conventional requirement. However, this is a sample file with the solution https://www.dropbox.com/t/bf4kLtD0RgWQpoWh

    This requires creating two calculated columns (it can be done with one column but would be too complex to implement)

    Rank = 
VAR IncidentTable = 
    CALCULATETABLE ( Incidents, ALLEXCEPT ( Incidents, Incidents[IncidentNumber] ) )
VAR IncidentCategoryTable = 
    CALCULATETABLE ( Incidents, ALLEXCEPT ( Incidents, Incidents[IncidentNumber], Incidents[Group Category] ) )
VAR UniqueDateTime = 
    Incidents[StartDate] + Incidents[EndDate_custom]
VAR Rank1 = 
    RANKX ( IncidentTable, Incidents[StartDate] + Incidents[EndDate_custom],, ASC ) 
VAR Rank2 =
    RANKX ( IncidentCategoryTable, Incidents[StartDate] + Incidents[EndDate_custom],, ASC ) 
RETURN
    Rank1 - Rank2

    Group Category  Rank = 
VAR IncidentTable =
    CALCULATETABLE ( Incidents, ALLEXCEPT ( Incidents, Incidents[IncidentNumber] ) )
VAR UniqueDate = Incidents[StartDate] + Incidents[EndDate_custom]
VAR PrevuiousTable =
    FILTER ( IncidentTable, Incidents[StartDate] + Incidents[EndDate_custom] <= UniqueDate )
VAR Ranks = 
    SELECTCOLUMNS ( PrevuiousTable, "@Rank", Incidents[Rank] )
RETURN
    COUNTROWS ( DISTINCT ( Ranks ) )
    • ruxandraalina's avatar
      ruxandraalina
      Icon for Helper I rankHelper I
      4 years ago

      Hello!

      Thank you very much!

      Unfortunately the second formula is simply not loading. I've tried on two different laptops, one with 8 GM RAM and another one with 16 and it's "working on it" for hours. I've tried restarting and refreshing everything multiple times, but with the same result. 

      Anyway, thanks a lot! 

      • tamerj1's avatar
        tamerj1
        Icon for Community Champion rankCommunity Champion
        4 years ago

        ruxandraalina 

        It is indeed heavy. I'm not sure if I can find another way to calculate this but I will try. I'll let you know if I was able to find something.