Forum Discussion

Moulick's avatar
Moulick
Frequent Visitor
9 years ago
Solved

Row level security in Power BI services

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...
General Comment
Interesting Links
Need Help
Tips and Tricks
  • v-caliao-msft's avatar
    v-caliao-msft
    9 years ago

    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

Anonymous's avatar
Anonymous
Not applicable
9 years ago

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's avatar
Moulick
Frequent Visitor
9 years ago

Hello  Anonymous

 


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's avatar
    v-caliao-msft
    Microsoft Employee
    9 years ago

    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