Create Ranking that Restarts with Date

[AlvinLy]

Hello,

I've been struggling with getting a rank working with my data as a measure. I need it as a measure and not as a column as I want it to be interactive with the user when they use a filter. I'll simplify my general problem to the below table. So imagine We have the following table:

Activity    FinishDate    Category   

Survey     2024-01-01  Observing

Count      2024-02-01  Observing

Arrange   2024-01-01  Organizing

Execute   2024-01-01  Organizing

Classify   2024-01-01  Organizing

Compile  2024-02-01  Organizing

Examine  2024-01-01  Analysis

Inspect    2024-03-01  Analysis

I want my new measure to be an grouped index based on the date, if there is a tie, I only care if the index is sequential and no duplicates, so the measure, when displayed would look like this:

Activity    FinishDate    Category       Index

Survey     2024-01-01  Observing         1

Count      2024-02-01  Observing         1

Arrange   2024-01-01  Organizing        2

Execute   2024-01-01  Organizing         3

Classify   2024-01-01  Organizing         4

Compile  2024-02-01  Organizing         2

Examine  2024-01-01  Analysis              5

Inspect    2024-03-01  Analysis              1

To clarify:

Dates 2024-01-01, it starts with Survey (1) --> Arrange (2) --> Execute (3) --> Classify (4) --> Examine (5). 

For dates 2024-02-01, it starts with Count (1) --> Compile (2)

Additionally, on my visuals page if say i filter to only see category "organizing" then it would look like this:

Activity    FinishDate    Category       Index

Arrange   2024-01-01  Organizing        1

Execute   2024-01-01  Organizing         2

Classify   2024-01-01  Organizing         3

Compile  2024-02-01  Organizing         1

I got this to work as a calculated column, but with a calculated column, the ranks stay with the row regardless of filtering in the visuals section.

[AlvinLy]

I apologize if I was not clear. I want the rank to be based on the date and if the date changes then the ranking would reset too. So to copy my first table again:

Activity    FinishDate    Category       Index

Survey     2024-01-01  Observing         1

Count      2024-02-01  Observing         1

Arrange   2024-01-01  Organizing        2

Execute   2024-01-01  Organizing         3

Classify   2024-01-01  Organizing         4

Compile  2024-02-01  Organizing         2

Examine  2024-01-01  Analysis              5

Inspect    2024-03-01  Analysis              1

So for dates 2024-01-01, it starts with Survey (1) --> Arrange (2) --> Execute (3) --> Classify (4) --> Examine (5). 

For dates 2024-02-01, it starts with Count (1) --> Compile (2)

To be honest, which one comes first or second in the same date period does not matter to me, to make it easy say alphabetical, then technically it would be:

Activity    FinishDate    Category       Index

Survey     2024-01-01  Observing        5

Count      2024-02-01  Observing         2

Arrange   2024-01-01  Organizing        1

Execute   2024-01-01  Organizing         4

Classify   2024-01-01  Organizing         2

Compile  2024-02-01  Organizing         1

Examine  2024-01-01  Analysis              3

Inspect    2024-03-01  Analysis              1

I hope that makes more sense, If not I can redo a simpler table

[vojtechsima]

Hi, @AlvinLy 

what about this:

DynamicRankResetPerDateMeasure = 
VAR SelectedDate = MAX('Table'[FinishDate])
RETURN
    COUNTROWS(
        FILTER(
            ALLSELECTED('Table'),
            'Table'[FinishDate] = SelectedDate
                && 'Table'[Index] <= MAX('Table'[Index])
        )
    )

The prerequisite for this is to have an Index column based on FinishDate sorted in ascending.
You can do that like this (Power Query):

    #"Sorted Rows" = Table.Buffer(Table.Sort(YourPreviousStep,{{"FinishDate", Order.Ascending}})),
    #"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Index", 1, 1, Int64.Type)
in
    #"Added Index"

[AlvinLy]

Is there a simple way to add an index column if my table is a calculated table. I am grabbing this information from an existing query, so I don't think the power query method would work for me. Additionally when I try to make an index using rankx the rows with the same date would count as the same value.

Also i went through the calculation you showed above, does the "ALLSELECTED" function interfere with any visual slicers I have to get the data to work properly even when slicing?

[AlvinLy · Accepted solution]

Hello All,

I found out how to do this if anyone was wondering. First, I created a calculated column of the rank. I know this method won't work if slicers are used to filter the data but this was my starting point. 

Rank =
VAR CurrentFinishDate = 'Table'[FinishDate]
VAR CurrentActivity = 'Table'[Activity]
VAR SameFinishDateActivities =
FILTER(
'Table',
'Table'[FinisDate] = CurrentFinishDate)
VAR SortedActivities =
ADDCOLUMNS(
SameFinishDateActivities,
"Ranktry",
RANKX(SameFinishDateActivities,'Table'[Activity], ,ASC))
RETURN
MAXX(
FILTER(
SortedActivities,
'Table'[Activity] = CurrentActivity),
[Ranktry])

 This created a rank column for me. Afterwards, I used the following measure:

Rank Measure = 
VAR CurrentFinishDate = SELECTEDVALUE('Table'[FinishDate])
VAR CurrentActivity = SELECTEDVALUE('Table'[Activity])
VAR CurrentRank = SELECTEDVALUE('Table'[Rank])
RETURN
IF(
CurrentRank = 1, 1, 
countrows(
Filter(
allselected('Table'),
'Table'[FinishDate] = CurrentFinishDate &&
'Table'[Rank] <= CurrentRank)))

This might be a bit roundabout so i'm open to suggestion for efficiency, but the data works so i'm not complaining. Just a quick think the reason i needed the measure is to use this in report builder. Thanks @vojtechsima , I think your answers got my brain working so I really appreciate the feedback.

[vojtechsima]

Hi, @AlvinLy ,
This should work for ranking based on the Finish Date:

DynamicRankByDate = 
VAR _rankedTable = ADDCOLUMNS(
    ALLSELECTED('Table'), 
    "rank", RANKX(ALLSELECTED('Table'), 'Table'[FinishDate], , ASC, DENSE)
)
VAR _currentDate = MAX('Table'[FinishDate])
VAR _currentRank = MAXX(
    FILTER(
        _rankedTable, 
        [FinishDate] = _currentDate
    ), 
    [rank]
)
RETURN
    _currentRank