Dynamic Slicer based on user Role/Email

[Anonymous]

Hi,

I'm trying to create dynamic slicer for my Power Bi reports which will filter the content of the slicer based on user email. The problem is in my org chart a single employee can report to multiple managers.

Manager	Employee
M1	E1
M2	E1
M3	E1
M1	M2
M1	M3
M2	E2
M2	E3
M3	E3
M3	E4

Based on this table i want E1 records to be accessible to M1,M2 and M3, but because M1 is also also a manager of M2 and M3 he should see records related to those 2 as well. 

Is it possible to create slicer which will react like that? Any tips would be appreciated.

[sebastiangestia]

Hello @ksusser,

From what I understand, the M1 deector has to see the records it has directly related E1, M2 and M3, and additionally the employees of M2 and M3 (i.e. E2, E3 and E4).

If so, you would have to create a table that has each "director" with "all the employees" that you can see, for this you could help with the PATH and PATHITEM functions (to find the "grandfather" of each employee), and then filter by the mail that corresponds to the co-responsible director (with LOOKUPVALUE in RLS).

Best regards!

[Anonymous]

Hi,

Thanks for providing such usefull tips. RLS seems to be a case to solve my issue partly. I am struggling with Path() function, because in my org structure a single employee can have 2 different managers. It breaks everything and i cant get over it. 

Table 1

Manager	Emp
M2	E1
M2	E2
M3	E2
M3	E3
M1	M2
M1	M3

Table 2

Emp	Ticket ID
E1	111
E1	222
E2	333
E2	444
E3	555

I want M1 to see all ticketing data, M2 to see E1 and E2 Tickets, and M3 to see E2 and E3 tickets. Is there a way to build path that leads to 2 different managers?
As always any tips are appreciated.

Best Regards

[sebastiangestia]

Hello @ksusser,

There are two ways to see it, one of the tickets to parents and grandparents (in this case would be to look for the entire path in Table1 with PATH(Emp, Director), although it would be necessary to add the M1 without grandfather), or vice versa, that each user had all the employees who depend on it (that is, that you would have to create a new table1 with the missing data of the grandparents and their grandchildren).

Best regards!

If you have the message, please consider accepting it as a solution. Welcome the kudos!

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

[amitchandak]

@Anonymous , This seems like Case of RLS ? Do you need only LOV or filter by employee ID too

RLS - Row Level security
https://community.powerbi.com/t5/MBAS-Gallery/Microsoft-Power-BI-Unleash-row-level-security-patterns-in-Power/td-p/712613
https://radacad.com/dynamic-row-level-security-with-organizational-hierarchy-power-bi
https://www.blue-granite.com/blog/using-dynamic-row-level-security-with-organizational-hierarchies
https://docs.microsoft.com/en-us/power-bi/service-admin-rls
https://blog.tallan.com/2018/04/10/row-level-security-in-power-bi-part-1-roles-and-users/
https://radacad.com/what-do-you-need-to-implement-dynamic-row-level-security-in-power-bi

[Anonymous]

Thanks for quick answer!

I will check the links you sent me. That's 1st time i hear about RLS and LOV so i am not really sure how to answer, but it seems that might be something i am looking for.