Show only aggregated data only

[Anonymous]

Hi!

I want to show certain users aggregated data only and restrict the ability to drill down to individual rows.

I have information on individual level for sick leave. Sick leave is related to a department and a table containing managers have user principal name and which department they run.

We have managers on levels A, B and C where only C-managers have direct employee responsibility.

Now we have the requirement that only C level managers see the individuals from the sick leave table and A and B managers only see aggregated data on their respective level. The employees and their sick leave is only on level C in the organisational structure.

Setting RLS for C-level managers works great but what about the A and B level managers? Since Power BI only calculates aggregated values on underlying data the A and B level managers will not see any data when those departments will be filtered out.

How would you solve this?

[v-angzheng-msft]

Thank you for sharing. Could you please mark your solution as Answered since it is working now? It will help the others in the community find the solution easily if they face the same problem with you. Thank you.

[Anonymous · Accepted solution]

Can this function be used inside a RLS function? Can we filter the departments table with username() and ISINSCOPE()?

If not, this function is not usable in our case Im afraid. Some of our models (SSAS Tabular on-prem) have up to 100 measures and what I gather this function will be used on each measure it applies. That would be a nightmare to develop let alone manage over time.

The only solution we have come up with is to pre aggregare a table which are open to all managers and then use RLS on the original table which have information on an individual level (raw data).

It would be useful to have a feature in Tabular models where you can control which user has access to a certain level in a hiearchy on the model level and not ony on individual measures.

[HashamNiaz]

Hi @Anonymous !

I think you need to think differently here, rather than hiding the information by applying RLS you can instead use ISINSCOPE() DAX function to check if the Mamager level is C then show the information else show BLANK()

ISINSCOPE() 

You can use below DAX to create a measure;

Sick Leave = IF(ISINSCOPE(Table[ManagerLevel]), [Measure to show Sick Value] , BLANK())

Regards,

Hasham

[Anonymous]

Thanks @HashamNiaz !

I will definitely look into that DAX function.

/Martin