Rank categories with duplicate categories

[ruxandraalina]

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!

[tamerj1]

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]

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]

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