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