Filter a table then rank

[Anonymous]

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.

[v-jayw-msft · Accepted solution]

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

[AilleryO]

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]

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 😉

[tamerj1]

Hi @Anonymous 
Can you please provide sample file?

[Anonymous]

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

[tamerj1]

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