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