If RANK result is not unique then RANK by Earliest Date

[queryuser]

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 on their complexity & impact giving a result in a column "Ranking" (see new column to the right - DAX)

So in case the ranking is the same 1:1 2:2 etc. how can I rank & display them based on submission time (see Desired Rank column)

Many thanks

[johnt75]

Is it possible to move the whole thing to Power Query? Your Ranking column looks like it could be done in Power Query, you would then be able to sort the table by both Rank and Time and add a index column as your new "Combined Ranking"

[Jihwan_Kim]

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
    )

[queryuser]

@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

[Jihwan_Kim · Accepted solution]

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

[queryuser]

Hello,

Not very sure if it works but thanks a lot for the effort. Will accept as a solution.

[amitchandak]

@queryuser ,

a new column

Rank = Rankx(Table, Table[Submission Time],,asc,dense)

or

Countx(filter(Table, [Submission Time] <= earlier([Submission Time]) ), [Submission Time])

[queryuser]

Hi @amitchandak 

Thanks for your quick reply.

So both options work only to rank the earlies time and does not consider the already existing rank of complexity.

So if 2 requests have the same complexity (ex. 1) then rank / prioritze the earlies request 

Here the Desired Rank would mean that the earlies request would be ranked first while the later one 2nd

How would you do that?