topic Re: How do I show only the ranking of one person? in DAX Commands and Tips

How do I show only the ranking of one person?

ShNBl84 — Fri, 10 Jul 2020 18:19:33 GMT

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?

Re: How do I show only the ranking of one person?

lbendlin — Fri, 10 Jul 2020 22:08:44 GMT

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.

Re: How do I show only the ranking of one person?

lbendlin — Fri, 10 Jul 2020 23:16:09 GMT

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.

Re: How do I show only the ranking of one person?

Anonymous — Mon, 13 Jul 2020 14:43:13 GMT

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

Re: How do I show only the ranking of one person?

ShNBl84 — Fri, 17 Jul 2020 20:44:57 GMT

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

Re: How do I show only the ranking of one person?

Anonymous — Fri, 17 Jul 2020 18:36:01 GMT

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-security

Regards,

Harsh Nathani

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

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

Re: How do I show only the ranking of one person?

ShNBl84 — Fri, 17 Jul 2020 20:45:24 GMT

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?

Anonymous