Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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?
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.
@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
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?
User | Count |
---|---|
47 | |
26 | |
22 | |
17 | |
15 |
User | Count |
---|---|
55 | |
34 | |
18 | |
17 | |
15 |