Forum Discussion
Create Ranking that Restarts with Date
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.
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
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"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?
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.
