Forum Discussion

Stu11's avatar
Stu11
Frequent Visitor
4 years ago
Solved

RankX Excluding Blanks

Hi,

 

I have the code below which is working. However how I can I add something so the rank ignores blanks?

 

"

Solved/Closed Ticket Ranking =
VAR Currentmonth = 'Summary Table'[MonthYear]
VAR Currentteam = 'Summary Table'[Team]
VAR newtable = filter('Summary Table','Summary Table'[MonthYear]=Currentmonth && 'Summary Table'[Team] = Currentteam)

RETURN

rankx(newtable,'Summary Table'[Numer of Solved/Closed Tickets],,DESC,Dense)"
 
Thanks,
 
Stu
Need Help
  • Stu11's avatar
    Stu11
    4 years ago

    Hi,

     

    It was ranking on the number of tickets.

     

    I added the following and it seems to have worked also.

    "

    Solved/Closed Ticket Ranking =
    VAR Currentmonth = 'Summary Table'[MonthYear]
    VAR Currentteam = 'Summary Table'[Team]
    VAR newtable = filter('Summary Table','Summary Table'[MonthYear]=Currentmonth && 'Summary Table'[Team] = Currentteam)

    RETURN
    if(not isBLANK('Summary Table'[Numer of Solved/Closed Tickets]),
    rankx(newtable,'Summary Table'[Numer of Solved/Closed Tickets],,DESC,Dense))"
     
    Thanks,

3 Replies

  • AilleryO's avatar
    AilleryO
    Memorable Member
    4 years ago

    Hi,

     

    Depends on the column where you want to skip the BLANKS but you can add a condition to your VAR newtable :

    newtable  = filter('Summary Table','Summary Table'[MonthYear]=Currentmonth && 'Summary Table'[Team] = Currentteam)&&NOT( ISBLANK( YourColumn ) ) )

     

    Hope it helps

    • Stu11's avatar
      Stu11
      Frequent Visitor
      4 years ago

      Hi,

       

      It was ranking on the number of tickets.

       

      I added the following and it seems to have worked also.

      "

      Solved/Closed Ticket Ranking =
      VAR Currentmonth = 'Summary Table'[MonthYear]
      VAR Currentteam = 'Summary Table'[Team]
      VAR newtable = filter('Summary Table','Summary Table'[MonthYear]=Currentmonth && 'Summary Table'[Team] = Currentteam)

      RETURN
      if(not isBLANK('Summary Table'[Numer of Solved/Closed Tickets]),
      rankx(newtable,'Summary Table'[Numer of Solved/Closed Tickets],,DESC,Dense))"
       
      Thanks,
      • AilleryO's avatar
        AilleryO
        Memorable Member
        4 years ago

        Good to know, and please don't forget to mark as solved for other members 🙂