Always return the 1st, 2nd, 3rd, etc. Rank from a Column

[PowerUser123]

So I have a table that has row level security like the below:

With row level security applied, a user may see the following table:

I want to create a measure that will return the 2nd highest Page value based on rank when RLS is applied. So the first table would result with "Dog". With RLS applied in the 2nd table, the result would be "Bird."

I would want to create measures for 1st, 2nd, 3rd, 4th, 5th, etc... so the measure would need to be dynamic enough to account for that.

[parry2k]

@PowerUser123 here is the measure to get the page name based on Rank = 2

2nd Ranked Page = 
VAR __tbl = ADDCOLUMNS ( VALUES ( 'Rank'[Page] ),  "@Page Rank", [Page Rank] )
RETURN
MAXX ( __tbl, IF ( [@Page Rank] = 2, [Page] ))

Check my latest blog post Comparing Selected Client With Other Top N Clients | PeryTUS  I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[parry2k]

@PowerUser123 one way is to add this measure and have visual level filter where Page Rank = 2

Page Rank = 
  
    RANKX ( ALLSELECTED ( 'Rank' ), CALCULATE ( MIN ( 'Rank'[Rank] ) ), , ASC ) 

Check my latest blog post Comparing Selected Client With Other Top N Clients | PeryTUS  I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[PowerUser123]

@parry2k Yes, that's a good idea and would work. However, I'm actually using this measure as part of an expression on a button. So I don't have the ability to set a visual level filter. I have to do everything within the measure expression

[parry2k]

@PowerUser123 on what value you are ranking? Or you are ranking on the column Rank?

[PowerUser123]

@parry2k Yes, ranking on the column Rank. I originally intended to use the calculated column, but the Rank is static with a calculated column even with RLS.

I just realized my rank is ascending vs descending but that doesn't really matter to me as I have rank columns going both directions.

I just need a measure that would return the 2nd highest/lowest value dynamically based on that table as RLS is applied.