Dynamic ranking without duplicates for Direct Query

Hi Anonymous 

Thanks for the reply from ThxAlot , please allow me to provide another insight.

The following measures are for your reference:

RIGHT Number = INT(MID(MAX([Name]), 6, LEN(MAX([Name])) - 5))

New Rank = RANK(DENSE,ALLSELECTED(risk),ORDERBY([Residual Risk (Value)], DESC, [RIGHT Number], DESC))

Output:

"there to be no duplicate ranks (risks that are rated the same value will then have the next rank number assigned based on name, i.e. liquidity risk and cyber security risk may both be rated as 20, cyber would be 1 and liquidity 2).
", based on your statement, I guess that if there are duplicate values, you need to sort by the first letter of name, then you can not use the [RIGHT Number] measure, you can directly modify [New Rank] measure as follows:

New Rank = RANK(DENSE,ALLSELECTED(risk),ORDERBY([Residual Risk (Value)], DESC, [Name], DESC))

Best Regards,
Yulia Xu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.