Forum Discussion

Boycie92's avatar
Boycie92
Icon for Resolver I rankResolver I
9 years ago
Solved

RANKX Help

Hi

 

I was wondering if someone could help me.

 

I want to create a measure that will work based on the following conditions:

 

I want it to show the top 4 Highest Performers and filter out everyone else. However I also want it to include a Unique Person in the Ranking. This person no matter what their score is will always be ranked as Number 4 unless they are ranked higher.

 

For Example

 

Department A:

 

Name           Rank    Score

Joe               1            9

Jen               2            5

Ben              3            4

Unique ID    4            2

 

OR

 

Department A:

 

Name           Rank    Score

Joe               1           10

Unique ID    2            7

Ben              3            4

Jen               4            1

 

I have the following columns:

 

Department 

Name

Score

 

Thanks in advance for the help.

Need Help
Tips and Tricks
  • v-sihou-msft's avatar
    v-sihou-msft
    9 years ago

    Boycie92

     

    Since you always want to include that specific person in "the four", you need to exclude that person in the dataset and rank the top 3 first, then UNION that person together. 

     

    Regards,

3 Replies

  • v-sihou-msft's avatar
    v-sihou-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    9 years ago

    Boycie92

     

    According to your description, you actually want to filter out top 3 performer and 1 specific Person, RANK all 4 people. Right?

     

    In this scenario, you can use TOPN() function to create a calculated table with the 3 best performers. Then use "Union Query" in Query Editor to combine a dataset with that specific person record only. Now you can create a calculated column to RANK all 4 people.

     

    Regards,

    • Boycie92's avatar
      Boycie92
      Icon for Resolver I rankResolver I
      9 years ago

      Hi v-sihou-msft,

       

      Thanks for your response.

       

      I already have the specific person within the data set. Do I still need to follow the process you recommended or can I just use the TOPN() function?

       

      Thanks,                                                                                                                                                                                      

      • v-sihou-msft's avatar
        v-sihou-msft
        Icon for Microsoft Employee rankMicrosoft Employee
        9 years ago

        Boycie92

         

        Since you always want to include that specific person in "the four", you need to exclude that person in the dataset and rank the top 3 first, then UNION that person together. 

         

        Regards,