How to lock measure not affected by RLS ???

[laurent_rio]

Hi i have chart that show total employee number and training hours per department. On the dashboard also will show average company training hours/staff ( total training hours for whole company/total staff) 

I want to create RLS where for example each department can only see their record. For example Finance only can see total empoloyee number in Finance dept and also training hours of them but i want to keep the measure to show average company training hours/staff as benchmarking 

When i do RLS, the measure will be affected . For example if i view as Finance, the average company training hours will only shown based on Finance only.

For example the average training hours when i see as whole or see as finance department should be same 55.68 hours

This is measure that i use for calculate average hours  

Company Average Training Hours = DIVIDE([Total Training Hours],[Total Employee])

Please kindly help,

Thanks

[Anonymous]

Hi @laurent_rio ,

Could you tell me if your problem has been solved? If it is, kindly Accept it as the solution. More people will benefit from it.

Best Regards,
Eyelyn Qin

[Anonymous · Accepted solution]

Hi @laurent_rio ,

Based on my test, the RLS filter the dataset directly. ALL() and ALLSELECTED() are unlikely to work in this case.

So my workround is to create a new column/table for the average calculation that could not be affected by RLS 

Avg Column = AVERAGEA('Table'[Training Hours])

Table 2 = SUMMARIZE('Table',"Avg of All",AVERAGE('Table'[Training Hours]))

The final output is shown below:

Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[laurent_rio]

Hi @amitchandak 

Thanks for you reply both looks not really straight forward but i will look into it first

Thanks

[amitchandak]

@laurent_rio two option to explore perspectives and object-level security

https://data-marc.com/2020/08/18/power-bi-visual-customization-using-perspectives/

OLS -https://powerbi.microsoft.com/en-us/blog/object-level-security-ols-now-available-for-public-preview-...