RLS syntax help needed

[Anonymous]

Hi community, 

For a couple of days now I'm trying to implement dynamic row level security (see my other posts), but without succes.. I think I thought of a solution, but I'm having trouble creating the correct syntax in the manage role section.

What is want is the following: based on the Email column of the RLS table, I want to filter the Fact table. Dependent on the value in the 'Level' column of the RLS table, I want filter on one of the five CCL(1 up to 5) columns. In words it would be something like this:

1. Create a table based with emailadres, CC-Name and Level, dependent on userprinciplename (e.g, If I login as andreas I want to only keep the first row of RLS table, if i login as Ian, I want to keep rows 3 and 4 of RLS table). 
2. Based on the associated level(s) If want to filter the fact table as follows:
      If Level = "CCL1", filter column Fact[CCL1] = RLS[CC-Name]
      If Level = "CCL2", filter column Fact[CCL2] = RLS[CC-Name]
      etc.

Please keep in mind that a specific emailadres can be associated to several CC-Names and propably also levels

Can somebody help me out?

Thanks so much in advance!

PS. the formula already in the screenshot works, but then I need to define the CCL level, which obviously I don't want. 

[amitchandak]

@Anonymous . refer if these two can help

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

[Anonymous]

Hi @amitchandak , 

Thanks for referring me to those docs. As I've read them before, I don't think they can help me with my problem. Are you able to look into the syntax? I'm already a step further:

IF(CONTAINS(Fact, [CCL1], LOOKUPVALUE(RLS[CC-Name], [Email], USERNAME()))=TRUE, 
Fact[CCL1] IN SUMMARIZE(FILTER(RLS, RLS[Email] = USERNAME()), RLS[CC-Name]),
IF(CONTAINS(Fact, [CCL2], LOOKUPVALUE(RLS[CC-Name], [Email], USERNAME()))=TRUE, 
Fact[CCL2] IN SUMMARIZE(FILTER(RLS, RLS[Email] = USERNAME()), RLS[CC-Name]),
IF(CONTAINS(Fact, [CCL3], LOOKUPVALUE(RLS[CC-Name], [Email], USERNAME()))=TRUE, 
Fact[CCL3] IN SUMMARIZE(FILTER(RLS, RLS[Email] = USERNAME()), RLS[CC-Name]),
IF(CONTAINS(Fact, [CCL4], LOOKUPVALUE(RLS[CC-Name], [Email], USERNAME()))=TRUE, 
Fact[CCL4] IN SUMMARIZE(FILTER(RLS, RLS[Email] = USERNAME()), RLS[CC-Name]),
IF(CONTAINS(Fact, [CCL5], LOOKUPVALUE(RLS[CC-Name], [Email], USERNAME()))=TRUE, 
Fact[CCL5] IN SUMMARIZE(FILTER(RLS, RLS[Email] = USERNAME()), RLS[CC-Name]))))))

 But it breaks whenever there are emailadresses who have two or more roles within the same Cost center level. 

Any help?

BR,