Dynamic ranking without duplicates for Direct Query

[Anonymous]

Dataset: Google Drive - Excel file - this is a desensitised dataset, and is not using Direct Query. 

PBI file 

Hi all,

I've been scouring the forums and other blogs for ages (and asking ChatGPT for help) with little success. 

We utilise a third party website for our business' risk management software. Their platform has Power BI integrated, but their default analytics is missing a couple of things - ranking risks being one of them. So I use Direct Query to connect to their live database which stores our risk data, and then can reupload the pbix file to their platform. 

I think I'm running into issues with ranking due to having to use Direct Query - I have read you can't use certain functions (e.g. EARLIER) and cannot add new columns, so needs to be a Measure.  

In short - I have a list of risks which have risk ratings. Some risks have the same rating. I'm using Direct Query so there may be limitations in functions. I need:

• it needs to be a measure, as cannot add new columns due to Direct Query
• each risk to be ranked based on rating (highest rated risk is 1)
• 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).
• the ranking to be dynamic/adjust based on filters, as we will filter by Business Unit

The current formula I'm using is below. It ranks but contains duplicates: 

*Ranking =

RANKX(

    ALLSELECTED('risk'),

    CALCULATE(MAX('risk'[Residual Risk (Value)])) * 100000 + RANKX(ALLSELECTED('risk'), MAX('risk'[Name]), , ASC),

    ,

    DESC,

    Dense

)

Please, any help is so deeply appreciated!!

[Anonymous]

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.

[ThxAlot]

[Anonymous]

Thank you @ThxAlot  and @Anonymous for your help. I really appreciate it and wish I had asked earlier!!

I am currently using ThxAlot's solution and it works as expected in terms of ranking without duplicates. In Service, however, when I apply a filter (such as business unit), the ranking adjusts accordingly as expected and the relevant risks go are at the top of the table correctly. BUT after the relevant risks, the table continues to include rows of data/risks that shouldnt be included (and the ranking measure applies a single ranked value to these incorrect risks). For example, I filter for BU01, and below the 7 correctly ranked risks are ALL other risks that should be excluded and are all ranked as 8. Is there a way to adjust this measure? 

[Anonymous]

Hi @Anonymous 

I tried the method provided by ThxAlot , then published this report to the service, did the filtering and everything showed up fine. 

Could you please provide screenshots of the problem you are experiencing? 

Or you can check if Ashish_Mathur‘s method meets your needs.

Best Regards,
Yulia Xu

[Ashish_Mathur]

Hi,

Try these measures

RR = SUM(risk[Residual Risk (Value)])

Measure = RANK(DENSE,ALLSELECTED(risk[Risk Rating],risk[UID],risk[Name]),orderby([RR],DESC,risk[Name],ASC))

Hope this helps.