topic Re: Ignore RLS and get the rank in DAX Commands and Tips

Ignore RLS and get the rank

Sri_phani — Fri, 23 Aug 2024 16:55:38 GMT

Hi,

I have Employee-wise RLS on the Active agent's table, but I have a separate table called All metrics that has KPIs (1-1 relationship). Now when I applied RLS the ranks show as 1. This is expected behaviour but while finding a workaround, I got to know by removing the relationship between these tables and using the TREATAS function we can get the rank.

I tried many ways but couldn't find a solution. Here is the measure used for finding team-wise rank for selected employees:

Team rank = VAR table_ = ADDCOLUMNS( FILTER( all('All Metrics'[Employee Name], 'All Metrics'[Supervisor 1 (Team Manager) ]), 'All Metrics'[Supervisor 1 (Team Manager)] = MAX('All Metrics'[Supervisor 1 (Team Manager) ]) ),"attain", [Resolve Attainment] ) Return Format(rankx(table_,[Resolve Attainment],,desc,Dense), 0)&"/"&COUNTROWS(table_)

Expected Result is like - 05/10

Re: Ignore RLS and get the rank

lbendlin — Fri, 23 Aug 2024 17:09:48 GMT

Read about CROSSFILTER(,,NONE) - that allows you to temporarily break relationships.

Re: Ignore RLS and get the rank

Sri_phani — Fri, 23 Aug 2024 17:11:51 GMT

lbendlin How do I incorporate Crossfilters in this measure?

Team rank =
VAR table_ =
ADDCOLUMNS(
  FILTER(
    all('All Metrics'[Employee Name],
       'All Metrics'[Supervisor 1 (Team Manager) ]),
           'All Metrics'[Supervisor 1 (Team Manager)] = MAX('All Metrics'[Supervisor 1 (Team Manager) ])
           ),"attain", 
    [Resolve Attainment]
)
Return
Format(rankx(table_,[Resolve Attainment],,desc,Dense), 0)&"/"&COUNTROWS(table_)

Re: Ignore RLS and get the rank

lbendlin — Fri, 23 Aug 2024 17:22:11 GMT

use CALCULATETABLE instead of FILTER

Re: Ignore RLS and get the rank

Sri_phani — Fri, 23 Aug 2024 17:32:43 GMT

lbendlin I tried using Calculatetable instead of filter, it is giving blanks as well in the result. Sorry, I am finding it hard to understand how can I put a stop to RLS while using Calculatetable function.

Re: Ignore RLS and get the rank

lbendlin — Fri, 23 Aug 2024 17:50:40 GMT

There is an alternative - use a shadow table that is disconnected from the data model.

Re: Ignore RLS and get the rank

Sri_phani — Fri, 23 Aug 2024 18:33:06 GMT

lbendlin Second table - ALL Metrics table is a disconnected table only on which Rank is created.