Different RLS Problem

[fatihcankara]

Hello,

I have a table named Budget and Employee in Power BI. The details of my Employee and Budget table are as follows. When my employee logs in powerbi login, I want him to check his login address with the e-mail address in the employee table and check the data in the department ID field in the row where his e-mail address is and in the ZZORG_D_ID field in the budget table. I want him to see the budget lines he is authorized for. Everything is perfect so far. The difficulty starts here. There can be one or more department IDs in the ZZORG_D_ID field separated by commas. When employee C logs in, he should be authorized to see budget items numbered 2,3,4. When employee D logs in, he should be authorized to see budget items numbered 3,4. When employee E logs in, he should be authorized to see budget items numbered 2,3,4. Another issue is that some employees work as managers of other departments on behalf of them. For example, employee A works in department numbered 10 and department numbered 20 on behalf of them. Thus, when logging in with the same e-mail address, the employee named A should be authorized to see the budget items with the Budget ID numbers 1,2,3,4.

The situations I encountered and did not work,
I created a new role in the Security section and used the '[Email] = USERPRINCIPALNAME()' DAX formula on the Employee tab, and the FILTER('Budget', CONTAINSSTRING('Bufget'[ZZORG_D_ID], SELECTEDVALUE('Staff'[Department ZORGID]))) DAX formula on the Budget page.

Thanks

Staff Nam	Email	Duty	Department ZORGID
A	a@a.com.tr	Sales	10
B	b@b.com.tr	Manager	20
C	c@c.com.tr	FI Manager	30
D	d@d.com.tr	BT Maneger	20
A	a@a.com.tr	Budget Manager	20

Budget Id	Company Code	Detail Budfet Code	Budget Name	Budget Amount	ZZORGID
1	F01	20.10.01.01	Budget A	100	10
2	F02	20.10.01.02	Budget B	2000	10,20
3	F01	20.10.01.03	Budget C	30000	10,20,30
4	F01	20.10.01.04	Budget D	50000	20,20

[Anonymous]

Hi @fatihcankara ,

You could create a security role and assign this role to your users to implement dynamic RLS. I've made a test for your reference:

[Email] ==  USERPRINCIPALNAME()

CONTAINSSTRING([ZZORG_D_ID],MAX(Staff[Department ZORGID]))

Best Regards,

Bof

[fatihcankara]

Hi, I tried this before. But it didn't work. Thanks.

[Anonymous]

Hi @fatihcankara ,

This works well for me. May I ask if you have assigned roles to your users in the semantic model of the BI Service?

Best Regards,

Bof

[lbendlin]

There can be one or more department IDs in the ZZORG_D_ID field separated by commas. 

Try to avoid that if possible.  Instead, have separate lines for these employees with a single department each per line.  Much easier to integrate into the data model.

[fatihcankara]

I'm working on different models, thanks.