Dynamic RLS hierarchy in power BI report

[Charu]

Hi Everyone,

I Couldn't achieve the RLS for organizational hierarchy please help me to get the solution for the following requirement.

Using Oracle database direct query mode.

When the manager logs in he should see his record(A), who is reporting to manager record (B)and as well as if there is anyone reporting to B that also manager should see.

For example, A is the manager, B is reporting to A and C, D are reporting to B.

when A logs in he should be able to see A, B, C, D records.

1. Employee details table having EmployeeId and reporting_to_ID
2.Sales table having transaction value,date,employee_id,reporting_to_ID,transaction_amount
relationship between employee-details and sales as one to many(employee_id).

unable to create calculated column for PATH function in direct query mode, so created measure but I got an error as "A table of multiple values was supplied where a single value was expected"

Followed this link http://radacad.com/dynamic-row-level-security-with-organizational-hierarchy-power-bi but no luck on

this.

Guide me how my tables structure should be.

Need help on this as soon as possible

Thanks in advance

[v-yuta-msft]

Hi Charu,

Path() is not supported in direct query mode. As a workaround, you may con create Manager ID columns(e.g.: level1, level2, ... ) of each level in hierarchy.  The values in each ManagerID columns are their UPNs. You can create a role using measure like [level1] = username() || [level2] = username || ..., then assign members to the role and try again.

Regards,

Jimmy Tao

[Charu]

Hi @v-yuta-msft

Please correct me if I'm wrong, are you saying to create level1, level2, etc in Oracle table and then use those columns in RoLE option.