Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Filter a table then rank

Please help.  I don't know how to do this and I keep going down rabbit holes.

 

I have a fact table, "Incidents", that contains column "Inc_CI_Service". 

I have a measure, "PM Count", that gives me the count of tickets for each CI Service for last month. 

I have another measure called "Trigger" that returns 1 if PM Count is greater than a variable or 0 if it is not.

 

 

I need a formula or set of steps to take to filter the list for Trigger = 1 and then Rank the resulting list/table.  Right now, RankX ranks the whole table but I need it to rank the filtered table.

 

I'm assuming it would involve the creation of a vitual table within the formula that ranks the results but I'm stuck on how to do that.  At this point, I can't even figure out how to create a filtered table based on 1 column and two measures.  

 

Please help.

Filtered table
RANKX
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi Anonymous ,

     

    You could consider to set the rank for records have trigger=1 as 999999 or 0, then calculate the rank for other value.

    For example:

    Measure 2 = IF([Measure]<1,99999,RANKX(ALL('Table'),CALCULATE(SUM('Table'[value])),,DESC,Dense))
    Or:
    Measure 2 = IF([Measure]<1,99999,RANKX(ALL('Table'),CALCULATE(SUM('Table'[value])),,ASC,Dense)-1)
     
    Best Regards,
    Jay

6 Replies

  • tamerj1's avatar
    tamerj1
    Icon for Community Champion rankCommunity Champion
    4 years ago

    Hi Anonymous 
    Can you please provide sample file?

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      I cannot.  Our company has suppressed the ability to upload files to the cloud sites.

      • tamerj1's avatar
        tamerj1
        Icon for Community Champion rankCommunity Champion
        4 years ago

        Can you paste sample data, measures code and sample expected  results?

  • AilleryO's avatar
    AilleryO
    Icon for Memorable Member rankMemorable Member
    4 years ago

    Hi,

     

    Hope I understood your needs, and here is something very close.

    In a new column to get the ranking for all as you did already I did on my data :

    Rank All = RANKX( ALL(ClassResults) , ClassResults[Total pts] )
    ClassResults being the name of my table and btwn [ ] name of column.
     
    And to get a ranking with filter on female students I did :
    Rank Female (not only) = RANKX( FILTER( ClassResults , ClassResults[Gender]="Female" ) , 
    ClassResults[Total pts] )
     
    If you do that you'll get a Ranking on every row (even the one not filtered), but if you check the results are correct. In my case the rabking of all female students is correct.
    If you want to get rid of the value on the non fitered lines, you can add in your column a test like :
    Rank Female (strictly) = 
    VAR CalcRank=RANKX( FILTER( ClassResults , ClassResults[Gender]="Female" ) , 
    ClassResults[Total pts] )
    RETURN
    IF( ClassResults[Gender]="Male" , BLANK() , CalcRank)
    And you'll get that :
     
     
    Hope it helps
     
    • AilleryO's avatar
      AilleryO
      Icon for Memorable Member rankMemorable Member
      4 years ago

      Or if you want both ranking, you can do :

      Rank by Gender = 
      VAR CurrGender=ClassResults[Gender]
      RETURN
      RANKX( FILTER( ALL(ClassResults), ClassResults[Gender]=CurrGender ) , ClassResults[Total pts] )
      to get that : 
      Just in case 😉
  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi Anonymous ,

     

    You could consider to set the rank for records have trigger=1 as 999999 or 0, then calculate the rank for other value.

    For example:

    Measure 2 = IF([Measure]<1,99999,RANKX(ALL('Table'),CALCULATE(SUM('Table'[value])),,DESC,Dense))
    Or:
    Measure 2 = IF([Measure]<1,99999,RANKX(ALL('Table'),CALCULATE(SUM('Table'[value])),,ASC,Dense)-1)
     
    Best Regards,
    Jay