Forum Discussion

laurent_rio's avatar
laurent_rio
Helper I
5 years ago
Solved

How to lock measure not affected by RLS ???

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

Need Help
Show and Tell
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    5 years ago

    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.

4 Replies