How to keep original values in a rankx measure and apply it on table with role level security?

[Anonymous]

Hi all,

I have a table like this:

The rank column in this created table is a caluclated measure that displays the ranking of each employee filtered by their channel and based on their total score:

Rank Employee = RANKX(

    ALL('table1'[Employee]),

    [Total Score]

)

This works fine. However, the table will be viewed by individual employees to see only their performance and I need to hide other employees rank and scores.

Right now, this is how the 2nd ranked employee from channel a views the table:

 It is showing his rank as 1 when it should be 2. Similarly, all employees view their rank as 1st.

Thus, how do i keep the original rank values even when there is role level security view being implemented?

[v-jianboli-msft · Accepted solution]

Hi @Anonymous ,

Approve with @chilichill. 
Based on your description I have created a simple sample:

Please try:

First create a summarize table:

Then hide it in the report view:

Rank the score:

Rank Employee =
VAR _a =
    FILTER ( ALL ( 'Summarize Score' ), [Channel] = MAX ( 'Score'[Channel] ) )
VAR _b =
    ADDCOLUMNS ( _a, "Rank", RANKX ( _a, [Score] ) )
RETURN
    MAXX ( FILTER ( _b, [Score] = MAX ( 'Score'[Score] ) ), [Rank] )

Output:

Create RLS:

Final output:

Best Regards,

Jianbo Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Thanks for the help and sorry for the late reply!

However, in my case, the "Score" column is also a calculated DAX measures that is affected by existing RLS in its formula (e.g. revenue earned by employee) and it could not be referenced using the "summarize" function. In other words, I need to reference the "Total Score" measure in your example instead of "Score" in my summary table... Its not posible to reference a calculated measure in the calculated table right?

[chilichill]

I don't think you can ignore RLS in measure, try with Column / Table.

https://learn.microsoft.com/en-us/power-bi/guidance/rls-guidance#design-partial-rls

[daXtreme]

My first answer would be: Create a measure that will return 1 if the row can be viewed by a user and 0 otherwise. Then use the Filter Pane and the measure to filter the visual (or the page? report?) to only show relevant rows.

[Anonymous]

@daXtreme 

Would it be possible to instead change the same measure and simply make it so it overwrites the role level view settings? If so, does anyhow have any idea how to write implement this addition to my existing measure?

[daXtreme]

I don't think a measure can overwrite (whatever that means...) RLS settings. Please consult the documentation but I'm pretty sure you can't do it. If you could do it so easily, it would be pointless, I guess, to have RLS at all.