How to use rankx with a subset to filter?

[Anonymous]

How to apply a filter condition to rankx DAX?

1. I am calculating a subset of people for whom the ranks are not supposed to be generated, the subset is the variable nt and below is my dax which considers all the username

OverallScoreVarianceRankNew =

var cmp = if([OverallScoreVariance]=[Overall %],0,if([Overall %]=blank(),0))

var nt = CALCULATE(max('KPI'[UserName]),filter('KPI',cmp=0))

var OverallScore = CALCULATE(CALCULATE(rankx(all('KPI'[UserName]),[OverallScoreVariance],,DESC,Dense),filter('KPI',[OverallScoreVariance]<>blank())),ALLEXCEPT('KPI','KPI'[UserName],'KPI'[WeekNew]))

return OverallScore

Now my question is how do I include a filter condition on the OverallScore variable which excludes the var nt = CALCULATE(max('KPI'[UserName]),filter('KPI',cmp=0))

Any help on this would be great.

[v-kelly-msft]

Hi  @Anonymous ,

Try:

var OverallScore = CALCULATE(CALCULATE(rankx(all('KPI'[UserName]),[OverallScoreVariance],,DESC,Dense),filter('KPI',[OverallScoreVariance]<>blank()&&[UserName]<>nt)),ALLEXCEPT('KPI','KPI'[UserName],'KPI'[WeekNew]))
 

If it doesnt help,could you pls provide some sample data with expected output for test?

Best Regards,
Kelly

Did I answer your question? Mark my reply as a solution!

[amitchandak]

@Anonymous , Try one of the two options

rankx(allselected('KPI'[UserName]),calculate([OverallScoreVariance],filter('KPI',[OverallScoreVariance]<>blank(),ALLEXCEPT('KPI','KPI'[NT_UserName],'KPI'[WeekNew]))),,DESC,Dense)

rankx(filter(allselected('KPI'[UserName]),filter('KPI',[OverallScoreVariance]<>blank(),ALLEXCEPT('KPI','KPI'[NT_UserName],'KPI'[WeekNew]))),calculate([OverallScoreVariance]),,DESC,Dense)

[Anonymous]

I have tried but it does not work @amitchandak 

[Anonymous]

thanks for your response but I am not sure if it was clear hence I have edited m question as well. I want to include the variable nt inside the rankx formula, so that it exclude some of my users who dont have a score or scores and variance are equal. 

rankx is not allwoing me to use the variable directly to filter, can you please suggest a workaround.

thanks @amitchandak