Row level security in Power BI services

[Moulick]

Hello ,
 
What i want to report  is rank of schools on the basis of number of students.(Higher the count, top on rankings)

I have published my report to power BI services , report is being seen on the basis of Row level security.

Row level security is implemented on the Schoolid , Let's say School with user Schoolid = 1 will able to see data only for school with id=1 

One of the measure ranks school amonst schools on the basis of number of student as mentioned above. But when user with Schoolid = 1 logs in , report do not have all data to evalute rankings. So ranking is being calculated on the basis of filterred data Schoolid = 1 . Which obviously will rank always 1

Rank Measure formula :  RANKX(ALL(table[Schoolid),[Total student],,0,Dense)

Is there any way that i can achive above requirement with the same Row level security by changing something in above formula?

Or i need to make this calculations before row level security comes into picture?

Please guide further on this.

Thanks

[Seth_C_Bauer]

@Moulick On a high level, the only way you would be able to do this is to have a seperate object that contained the school and student count (information you needed for the rank). This would not be filtered by RLS because there would be no relationship. RLS is going to filter every aspect of your model where there are relationships tied to the RLS filtered object, so this seperate object would be seen by all users because it would't be filtered... I would imagine you could hide this from the end user and still reference it in your calculation somehow... That would be the approach I would explore.

[Moulick]

Hello  @Seth_C_Bauer

Thank you so much for the reply.

I was thinking in sync with the solution which you are suggesting that take a similar object which is completly out of influence of RLS. Still there's a problem which i am protrying you below in context of the same example as mentioned in question above.

Let's say i have one fact table 'FTSchools' which contains all the fact data regarding schools. and another is demension table 'DMSchoolsMaster' which contains only master data related to schools.

Now above both the tables are having relationship on a column 'SchoolId' and RLS is being applied on 'DMSchoolsMaster' so when user will login as schoolId='1' both table will get filter on schoolId='1'

When we calculate rankings obviously we need all the records of 'FTSchools' so to overcome RLS i am bringing another 'FTSchools_Copy' which is exact replica of 'FTSchools' , All the ranking formulas are being calculated on 'FTSchools_Copy'.

All seems good in above approch , should it work?

Unfortunately it's not working , What is happening as How would formula on 'FTSchools_Copy' would get to know what ois being selected on 'FTSchools' ? becuase ultimately ranking is being calculated for a school being selected from 'FTSchools' as per RLS. and we can not set a relationship between 'FTSchools' and 'FTSchools_Copy' as if we set then both the table will be end up with same data under RLS.

Please let me know if something is unclear in above explaination.

[v-caliao-msft · Accepted solution]

Hi @Moulick,

Do you create a rank measure in your report? I have tested it on my sample data, if we create calculated column instead of measure, then you can get the expect rank.

Test Data

Create a summarize table
Table = SUMMARIZE(Table2,Table2[SchoolName],"CountStudent",CALCULATE(COUNTA(Table2[StudentName]),ALLEXCEPT(Table2,Table2[SchoolName])))

Create a rank column
Rank = RANKX('Table','Table'[CountStudent])

Create a role

Then test this role, I can get expected result.

Regards,

Charlie Liao