topic Re: Filter a table then rank in DAX Commands and Tips

Filter a table then rank

Anonymous — Tue, 10 May 2022 18:11:38 GMT

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.

Re: Filter a table then rank

tamerj1 — Wed, 11 May 2022 10:39:51 GMT

Hi Anonymous
Can you please provide sample file?

Re: Filter a table then rank

Anonymous — Wed, 11 May 2022 15:21:57 GMT

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

Re: Filter a table then rank

tamerj1 — Wed, 11 May 2022 15:27:32 GMT

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

Re: Filter a table then rank

AilleryO — Wed, 11 May 2022 15:58:03 GMT

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

Re: Filter a table then rank

AilleryO — Wed, 11 May 2022 16:13:47 GMT

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 😉

Re: Filter a table then rank

Anonymous — Fri, 13 May 2022 09:37:18 GMT

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