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