Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
ShNBl84
Helper II
Helper II

How do I show only the ranking of one person?

I have a table of employees and their scores. The employees use row level security when they log in to view the report. I am trying to create a measure that displays ONLY their ranking number.

 

My setup is based on two tables: one has their names and scores and is subject to the RLS and the other table is the same but is not subject to the RLS. There is no relationship connection between them so that I can filter the first table by RLS and compare those results to a benchmarking table with no data filtered.

If that is too confusing, imagine a table with their names only that has no relationship to a table with their names and scores and I want to use a slicer from the first table to show just the rank score (probably using SELECTEDVALUE() or something).

 

I got as far as making the RANKX measure successfully, but it shows all names and all ranks.

 

How do I make a measure that results in only the number rank of the individual? 

6 REPLIES 6
Anonymous
Not applicable

Well, the second table that's not subject to RLS should store the rank value next to each name. Then you can just retrieve this value based on the currently visible name in the RLS-controlled table using FILTER and some scalar returning function. Or, you can connect the two tables on names and then the RLS-controlled table will filter the right row and you can harvest the value with SELECTEDVALUE.

Best
D
lbendlin
Super User
Super User

I don't think Lookupvalue can work in such a scenario as it probably requires a column at the other end. Let's try with some sample data.

I think you will have to make the ranking a static value (a column), defined before DAX. Either in Power Query or at the source. It can be a ranking with gaps (caused by RLS), and then you can do a rankx over that column to arrive at the individual's rank among the people exposed by RLS. 

I think I can create a calculated column on the table without RLS then use LOOKUPVALUE or SELECTEDVALUE to search for that salesperson's rank. However, I can't get the ranking to work. I have looked at 8 other answers so far to do RANKX but none of them have worked. The right column is the salesperson ranking based on total overcharges. Notice that salesperson "sss" is both rank 1 and 4.

ShNBl84_0-1595009776732.png

@Anonymous @lbendlin 

Hi @ShNBl84 ,

 

 

Create a new Table

 

New Table = SUMMARIZE ('Table', 'Table'[SalesPerson],'Table'[Overcharge])

 

 

Then create a RANK column in this Table

 

Calculated Column = RANKX ( 'New Table'[SalesPerson], 'Table'[Overcharge],,DESC) 

 

 

Check if these 2 links help for Data Masking

 

https://radacad.com/what-do-you-need-to-implement-dynamic-row-level-security-in-power-bi

https://radacad.com/secure-the-sensitive-data-in-power-bi-data-masking-better-with-row-level-securit...

 


Regards,

Harsh Nathani


Appreciate with a Kudos!! (Click the Thumbs Up Button)

Did I answer your question? Mark my post as a solution!

 

I would like to have a date slicer in there to see rank by time frame, so a SUMMARIZE would not help. Does RANKX only work on tables with one row per salesperson?

 

@harshnathani 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors