Dynamic RLS - keep rows in fact table when either column A or column B matches condition

[Anonymous]

Hello,

I'd like to set up dynamic RLS in a way that rows in the fact table are kept when either 'fact'[Team_assessment] or 'fact'[Team_implementation] matches 'RLS'[Team_coordinator].

Table 'fact'

Id_Idea	Team_assessment	Team_implementation
1	red	red
2	blue	blue
3	red	blue

Table 'RLS'

id_coordinator	Email_coordinator	Team_coordinator
1	1@gmail.com	red
2	2@gmail.com	blue
3	3@gmail.com	red

Expected Result

id_Idea	Team_assessment	Team_implementation	is seen by
1	red	red	1, 3
2	blue	blue	2
3	red	blue	1, 2, 3

This would be easy if only 'fact'[Team_assessment] or 'fact'[Team_implementation] had to be filtered by 'RLS', since basic dynamic RLS would be sufficient.

I already created a role 'role 1' Email_coordinator = USERPRINCIPALNAME() and a one column bridge table 'bridge' containing unique values of 'RLS'[Team_Coordinator]. 

Could someone provide me a link or a suggestion on how to go about this problem?

Many thanks in advance.

[Anonymous · Accepted solution]

Hi @Anonymous ,

Please have a try.

[Id_Idea] in CALCULATETABLE(VALUES('Table'[Id_Idea]),FILTER(ALL('Table'),'Table'[Team_assessment]=CALCULATE(MAX('Table (2)'[Team_coordinator]),'Table (2)'[Email_coordinator]=USERPRINCIPALNAME())
||'Table'[Team_implementation]=CALCULATE(MAX('Table (2)'[Team_coordinator]),'Table (2)'[Email_coordinator]=USERPRINCIPALNAME())
))

Best Regards
Community Support Team _ Polly

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

[Anonymous]

Hi v-polly-msft

This is exactly what I was looking for. Thank you so much.

Best regards and many thx

[Anonymous]

Hi @Anonymous ,

Please have a try.

Create a measure.

Measure = 
var cur_user=USERPRINCIPALNAME()
var td=CALCULATE(MAX('Table (2)'[Team_coordinator]),'Table (2)'[Email_coordinator]=cur_user)
var _str=SELECTEDVALUE('Table'[Team_assessment])&SELECTEDVALUE('Table'[Team_implementation])
return
IF(CONTAINSSTRING(_str,td),1)

How to Get Your Question Answered Quickly 

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards
Community Support Team _ Polly

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

[Anonymous]

Hi v-polly-msft,

Thank you for taking your time and the detailed answer.

This workaround using a visual-level filter works perfectly, but only for a table visual. Unfortunately I have lots of measures in my report which are based on the fact table. Is there a way to implement your logic as a table filter dax expression in the Manage Roles tab? 

Best regards