topic Rank categories with duplicate categories in DAX Commands and Tips

Rank categories with duplicate categories

ruxandraalina — Thu, 09 Jun 2022 07:58:36 GMT

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!

Re: Rank categories with duplicate categories

tamerj1 — Thu, 09 Jun 2022 10:43:44 GMT

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 ) )

Re: Rank categories with duplicate categories

ruxandraalina — Fri, 10 Jun 2022 07:11:40 GMT

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!

Re: Rank categories with duplicate categories

tamerj1 — Fri, 10 Jun 2022 08:49:05 GMT

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.