RANKX with multiple conditions

[Karim_Hossam]

I am trying to use RANkx fonction, to rank the participants of a quiz (hosted in sharepoint list)  in power Bi, based on the below criteria:

First in the rank:  The quicker participans to answer the questions (time) + they didn't click on a hint button + they get the correct answer form first trial

secondary in the  rank: The quicker participants to answer the questions (time) + they clicked on a hint button + they get the correct answers form first trial

Last in the rank  : The quicker participants to answer the questions (time) + they got the correct answers form more than one trial.

In the table I have the below coloumns:

-Participants name

-Time: (time to answer the questions)

-Click: (if the user has clicked on the hint button (yes or no))

-Correct  answers: (if the user got the right ansers(yes or no)

-trial count :(number of trials to get correct answers)

any idea how I can built this fonction?

Thanks

[Anonymous · Accepted solution]

Hi @Karim_Hossam ,

I create a table as you mentioned.

Then I create a calculated column.

Ranking Metric =
VAR TimeScore = [Time]
VAR HintPenalty =
    IF ( [Click] = "yes", 0.5, 0 )
VAR CorrectFirstTry =
    IF (
        'Table'[Correct  answers] = "yes"
            && [trial count] = 1,
        1,
        IF ( [Correct  answers] = "yes", 0.75, 0 )
    )
RETURN
    TimeScore - HintPenalty + CorrectFirstTry

Finally I create a new calculated column and you can get what you want.

Participant Rank = RANKX(ALL('Table'), 'Table'[Ranking Metric], , ASC, Dense)

Best Regards

Yilong Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[jupsimarsingh]

Hello ,

You can create a classification column using Switch statement and then use RankX to rank participants based on the classification and their time to answer:
Something like this :
ParticipantClassification =
SWITCH(
TRUE(),
[Correct answers] = "yes" && [Click] = "no" && [trial count] = 1, 1, -- First priority
[Correct answers] = "yes" && [Click] = "yes" && [trial count] = 1, 2, -- Second priority
[Correct answers] = "yes" && [trial count] > 1, 3, -- Last priority
4 -- Default value for participants who don't meet any criteria
)


Create a Ranking and using RankX:
ParticipantRank =
RANKX(
FILTER(
'YourTable',
'YourTable'[ParticipantClassification] = EARLIER('YourTable'[ParticipantClassification])
),
'YourTable'[Time],
,
ASC
)

You can also combine both to get  the final ranking considering both the classification and the time, you can create another calculated column.

Please mark is as solved solution if this resolved your query ! Thanks