Forum Discussion

Emmy66's avatar
Emmy66
Helper V
5 years ago
Solved

Ranking

Hi,

 

Your help on this will be appreciated.

I'm trying to do top 5 projects by users using the below dax.

Top 5 Projects =
VAR ProjRank = RANKX(ALL(User[First_LastName]),[NoOfProjects],,DESC)
RETURN
IF(ProjRank<=5, [NoOfProjects], BLANK())
It returns the result below
NameTop 5
AB5
BB4
AC3
AD2
BC1
CC1
CD1
EF1
FF1
EE1
AA1
BF1
Please note, [NoOfProjects] is a measure.
Is there a way I can restrict it to return only 5 records as shown below?
NameTop 5
AB5
BB4
AC3
AD2
BC1

 

Thanks in advance.

Need Help
  • Emmy66's avatar
    Emmy66
    5 years ago

    Hi parry2k, thanks for your reply. Apologies for the delay in responding to your message.

    Please find the link to the PBIX file. My expected output is as per below screenshot. The 5th one can be any user with 1 project. 

    My measures are all included in the PBIX file.

    Thanks in advance.

7 Replies

  • parry2k's avatar
    parry2k
    Super User
    5 years ago

    Emmy66 Glad to see it worked, sorry didn't have a chance to look at your file yesterday. Eventually, it was a matter of breaking the ties which you figure out, well done. 

     

    Cheers!!

    P

  • parry2k's avatar
    parry2k
    Super User
    5 years ago

    Emmy66 you can simply use TopN in the filter pane rather than writing a Rank measure. 

     

    Check my latest blog post Compare Budgeted Scenarios vs. Actuals I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

    ⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

  • parry2k's avatar
    parry2k
    Super User
    5 years ago

    Emmy66 looking closely, you need to break the tie, seems like the one you are getting rank 1, probably number of projects are the same and that's why they are getting the Rank 1, check here on how to break the tie (scroll down and read Break Tie section)

     

    Check my latest blog post Compare Budgeted Scenarios vs. Actuals I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

    ⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

     

     

    • Emmy66's avatar
      Emmy66
      Helper V
      5 years ago

      Hi parry2k , thanks for your prompt reply, very much appreciated. I've tried out the approach on the link you mentioned but it didn't work for me (See below).

      On using TopN filter, I also tried that but it returns all the records as I mentioned earlier which I believe is because of the ties.

      VAR DepartRank = RANKX(ALL(User[First_LastName]),([NoOfProjects]+INT(CALCULATE(MIN(Dates[Date]))) / 100000))
      RETURN
      IF(DepartRank<=5, ([NoOfProjects]+INT(CALCULATE(MIN(Dates[Date]))) / 100000), BLANK())
       
      Any further ideas/solution will be appreciated.
      • Emmy66's avatar
        Emmy66
        Helper V
        5 years ago

        I have resolved the issue using rand() by applying these methods.

        Top 5 Projects =
        VAR ProjRank = RANKX(ALL(User[First_LastName]),[NoOfProjects],,DESC)
        RETURN
        IF(ProjRank<=5, [NoOfProjects], BLANK())
        2nd: TieBreaker = Top 5 Projects +Rand() and then used TopN to select Top5 based on TieBreaker measure. 
        Thanks again parry2k for trying to assist.
  • parry2k's avatar
    parry2k
    Super User
    5 years ago

    Emmy66 Did you see if the rank ties did break after you added the logic? It will be easier if you share pbix file using one drive/google drive with the expected output. Remove any sensitive information before sharing.

    • Emmy66's avatar
      Emmy66
      Helper V
      5 years ago

      Hi parry2k, thanks for your reply. Apologies for the delay in responding to your message.

      Please find the link to the PBIX file. My expected output is as per below screenshot. The 5th one can be any user with 1 project. 

      My measures are all included in the PBIX file.

      Thanks in advance.