RLS implementation and Organizational hierarchy data display in PBI report

[smurug]

Hi All -

We have implemented both RLS and measure calculations for organizational hierarchy similar to the approach mentioned in the following link: https://www.daxpatterns.com/parent-child-hierarchies/ 

However there are two issues,

1) The data is filtered as expected in the tables, however as the levels are defined statically the data is always displayed from the top level hierarchy for all the users. (i.e.,) if a user from Level 3 is accessing the report even though the data is filtered correctly for him it is rolled up to the top hierarchy and displayed because of the static values in the level columns.
2) Blank rows or repeated display of the summary rows

Currently we are more concerned on the first issue, following are the few things we have tried without any luck so far,
1) Get the user context in the table and assign the values for the level columns dynamically - However the username or userprincipal name is not accessible in the table. It is only available to use in a measure

2) To get the minimum value in the hierarchy depth column after the row level filter is applied on the table. Currently even if the table rows are filtered correctly based on the login, the calculation of the minimum value in the hierarchy depth using any of the dax functions / logic is giving the minimum value with respect to the entire table and not the filtered rows

Further as the original approach is using the tabular model as the source for Power BI reports, is there a way to pass the user information back to the SQL DB (through SSAS tabular) and filter the rows while it is being fetched into the tabular model for the user table alone. Tried a few ways but was not able to find a way to get teh user information back to the SQL DB.

Is there something we are missing in the above aproaches or a different approach is present using which this can be acheived. Any suggestions / pointers would be really helpfull.

[v-shex-msft · Accepted solution]

HI @smurug,

>>Tried a few ways but was not able to find a way to get teh user information back to the SQL DB.

Power bi only support get data from datasource, current it not contains any write back features to return information.

For your requirement, I'd like to suggest you to get current user from database, then use these user info to create mapping table to apply RLS filter effect.

Reference link:

SQL Server - current user name

In my opinion, if you want to achieve hierarchy level RLS, I'd like to suggest you apply security filter on different columns instead of direct filter on hierarchy column.

AFAIK, measures who contains specific filters not works properly on summary level, you can take a look at following link about how to work through this issue.

Clever Hierarchy Handling in DAX

BTW, power bi not support setting RLS for live connection mode, you need to use SSDT to deal with them on database side.

Tutorial: Dynamic row level security with Analysis services tabular model

Regards,

Xiaoxin Sheng