Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago

Dynamic ranking without duplicates for Direct Query

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!!
Need Help

5 Replies

    • Anonymous's avatar
      Anonymous
      Not applicable
      1 year ago

      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? 

      • Ashish_Mathur's avatar
        Ashish_Mathur
        Super User
        1 year ago

        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.

         

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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.