Dynamic ranking without duplicates for Direct Query

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?