Forum Discussion
Solved
If RANK result is not unique then RANK by Earliest Date
Hello, Can I ask for your help? How can I rank request that are similar in rank bu thave different submission times. (Earliest is priority) So there is a ranking in place for requests based o...
Hi,
I am not sure if I understood your question correctly, but if you are looking for creating a measure, please try the below. The first suggestion was creating a new column.
Desired Ranking measure: = VAR newtable = ADDCOLUMNS ( ALL ( Report_Complexity ), "@newrank", VAR timerank = CALCULATE ( RANKX ( ALL ( Report_Complexity ), CALCULATE ( MAX ( Report_Complexity[Submission Time] ) ), , ASC ) ) VAR ranking = CALCULATE ( IF ( SELECTEDVALUE ( Report_Complexity[Complexity Indicator] ) = "Low" && SELECTEDVALUE ( Report_Complexity[Impact indicator] ) = "High", 1, IF ( SELECTEDVALUE ( Report_Complexity[Complexity Indicator] ) = "High" && SELECTEDVALUE ( Report_Complexity[Impact indicator] ) = "High", 2, IF ( SELECTEDVALUE ( Report_Complexity[Complexity Indicator] ) = "Low" && SELECTEDVALUE ( Report_Complexity[Impact indicator] ) = "Low", 3, IF ( SELECTEDVALUE ( Report_Complexity[Complexity Indicator] ) = "High" && SELECTEDVALUE ( Report_Complexity[Impact indicator] ) = "Low", 4 ) ) ) ) ) RETURN timerank + ranking * 10000 ) RETURN IF ( HASONEVALUE ( Report_Complexity[Request] ), RANKX ( ALL ( Report_Complexity ), CALCULATE ( SUMX ( FILTER ( newtable, Report_Complexity[Request] = MAX ( Report_Complexity[Request] ) ), [@newrank] ) ), , ASC ) )
Hi,
Please check the below picture and the attached pbix file.
Desired Ranking CC =
VAR newtable =
ADDCOLUMNS (
Report_Complexity,
"@newrank",
VAR timerank =
RANKX ( Report_Complexity, Report_Complexity[Submission Time],, ASC )
VAR ranking =
IF (
Report_Complexity[Complexity Indicator] = "Low"
&& Report_Complexity[Impact indicator] = "High",
1,
IF (
Report_Complexity[Complexity Indicator] = "High"
&& Report_Complexity[Impact indicator] = "High",
2,
IF (
Report_Complexity[Complexity Indicator] = "Low"
&& Report_Complexity[Impact indicator] = "Low",
3,
IF (
Report_Complexity[Complexity Indicator] = "High"
&& Report_Complexity[Impact indicator] = "Low",
4
)
)
)
)
VAR newranknumber = ranking * 100 + timerank
RETURN
newranknumber
)
RETURN
RANKX (
newtable,
MAXX (
FILTER (
newtable,
Report_Complexity[Request] = EARLIER ( Report_Complexity[Request] )
),
[@newrank]
),
,
ASC
)
Jihwan_Kim
Thank you for the effort! It almost works. The issue here is that the initial RANK looses it's order when submitted date is way later. So any request ranked as 2 in this case shall be #3 instead of 6
Hi,
I am not sure if I understood your question correctly, but if you are looking for creating a measure, please try the below. The first suggestion was creating a new column.
Desired Ranking measure: = VAR newtable = ADDCOLUMNS ( ALL ( Report_Complexity ), "@newrank", VAR timerank = CALCULATE ( RANKX ( ALL ( Report_Complexity ), CALCULATE ( MAX ( Report_Complexity[Submission Time] ) ), , ASC ) ) VAR ranking = CALCULATE ( IF ( SELECTEDVALUE ( Report_Complexity[Complexity Indicator] ) = "Low" && SELECTEDVALUE ( Report_Complexity[Impact indicator] ) = "High", 1, IF ( SELECTEDVALUE ( Report_Complexity[Complexity Indicator] ) = "High" && SELECTEDVALUE ( Report_Complexity[Impact indicator] ) = "High", 2, IF ( SELECTEDVALUE ( Report_Complexity[Complexity Indicator] ) = "Low" && SELECTEDVALUE ( Report_Complexity[Impact indicator] ) = "Low", 3, IF ( SELECTEDVALUE ( Report_Complexity[Complexity Indicator] ) = "High" && SELECTEDVALUE ( Report_Complexity[Impact indicator] ) = "Low", 4 ) ) ) ) ) RETURN timerank + ranking * 10000 ) RETURN IF ( HASONEVALUE ( Report_Complexity[Request] ), RANKX ( ALL ( Report_Complexity ), CALCULATE ( SUMX ( FILTER ( newtable, Report_Complexity[Request] = MAX ( Report_Complexity[Request] ) ), [@newrank] ) ), , ASC ) )Hello,
Not very sure if it works but thanks a lot for the effort. Will accept as a solution.