Hello All,
We currently have all of our reports with RLS. There is a single report where we would like to include a rank pertaining to how the sales person ranks within the entire enterprise. Is there a way to override the RLS for a single card visualization that would display the sales rep’s overall rank without loading a stagnant table?
Please note that I’m only a few months into Dax and Power BI. I’ve attempted RANKX ( = RANKX( ALL ( ‘Table’ ), [Measure] , , DESC ) however when I test this, every sales rep is ranked #1.
I’ve searched for these topics on YouTube but can’t seem to find anything this specific. I also have not yet completed the online courses so if you could simply point me to a solution, it would be greatly appreciated.
Thank you,
John
Hi John,
Sorry for the delay.
RANKX can be a little confusing I know.
It would be helpful to see more of your example here because there are a number of nuances to RANKX and all depends on the exact ‘context’ of the calculation.
I completed a detailed workshop on RANKX here if you want to review it. I cover many variations on how to it works depending on what context you have.
If you are receiving 1 for every result it’s highly likely the table parameter (ie. ALL( table ) ) is not what you need in the formula. You likely need something else.
It’s hard for me to tell exactly though without seeing more.
If you have a demo file to share that’s the easiest way to get a fast reply, with a good answer.
Thanks
Sam
Thank you for the response. I attended the Learning Summit with the ranking techniques and learned a great deal. After reviewing this today, I’m still having an issue.
The RANKX is working perfectly fine to rank our entire Enterprise in an admin view. The problem comes when we introduce row level security and when the Agents log into the online platform.
In this view below, we can see that Agent “3” is ranked 3rd in the Enterprise. When Agent 3 logs into their profile, they are only privy to their own data so the Card Visual that shows the rank of 3 changes to rank 1 on their view.
The sample table is set up shows agent # 1 - # 124 and their Premium Rank.
When I View as Roles by Director 10 (or filter by Director 10) , you can see how the filters work properly.
I’d like the ranking to show their actual rank based on the enterprise, or in other words override the row level security.
Here is a sample file with some bare bones.
Sample for DNA RANKX and Row Level Security.pbix (40.4 KB)
Hopefully this is a little more clear and thank you in advance for your assistance.
John
Interesting.
I honestly think the best this to do here for this is to generate a calculated table with all the rankings essentially hard coded into a table. This way there would be no re-calculating of the results based on any other filters or slicers that are placed over the report page.
Have you thought of doing it this way? Do you see any issues with this?
I don’t believe you can override RLS within a formula. I could be wrong but that’s my understanding at the moment.
When you generate calculated tables the numbers are static and don’t change. This would solve your RLS issue I believe.
Let me know what you think and if this could work for you.
Sam
Thank you.
I had thought about that and will give it a shot. Time to learn something new with calculated tables. I’ll circle back when I get hung up.
John