Dynamic RLS with multiple columns filter

[adrianPadurariu]

Hello,

I need to implement a complex RLS with dynamic column filtering based on email address. I have a fact table containing the columns from the RLS table and the RLS table below:

Email	Entity	Target Segment	Survey Type	Equipe
astrid.n@	ALL	ALL	ALL	ALL
aliane.b@	France	ALL	ALL	ALL
martine.t@	France	Merchant	Global Relationship	ALL
ina.s@	France	User	Customer Care	CRC B2C CESU Comearth, CRC B2C Carte Comearth
cedric.m@	France	User	Customer Care	CRC B2C CESU Comearth, CRC B2C Carte Comearth
idrissia.l@	France	Client	Customer Care	CRC BtoB MC ERF
lamia.a@	France	Merchant, User	Customer Care	CRC BtoB MC ERF
valeria.z@	Italy EB, Italy FM	ALL	ALL	CRC BtoB MC ERF
david.b@	France	Client	Customer Care	CRC BtoB SAV Sitel, CRC BtoB MC Sitel, CRC BtoC Carte Sitel, CRC BtoB MC Sitel Blois, CRC BtoC Carte Sitel Blois
christian.l@	France	User, Merchant	Customer Care	CRC BtoB SAV Sitel, CRC BtoB MC Sitel, CRC BtoC Carte Sitel, CRC BtoB MC Sitel Blois, CRC BtoC Carte Sitel Blois
adrian.p@	Italy FM	Merchant	Customer Care	ALL

I the end, this table will have approximately 300 email addresses, each with a specific filter, so it needs to be dynamic.

"All" means that needs to have access to all data from that column.

[adrianPadurariu · Accepted solution]

Solution!

I have created some Dim Tabels for every column: Dim_Entity, Dim_Target Segment , Dim_Survey Type, Dim_Equipe. These table are related to the Fact table.

I also imported the Access table, the one in the original post. This one is not related to the fact.

In the modeling I have created a Role filtered for every Dim_Table with the folowing code:

VAR _Restriction =
CALCULATETABLE(
VALUES(Access[Entity]  ------This will be changed with the column from every Dim_Table---------),
Access[Email]=USERPRINCIPALNAME()
)
VAR _RLS_Restriction =
SWITCH(
TRUE(),
OR(
"ALL" IN _Restriction,
[Entity] (------This will be changed with the column from every Dim_Table---------) IN _Restriction),
TRUE(),
FALSE()
)
RETURN
_RLS_Restriction

[v-jiewu-msft]

Hi @adrianPadurariu ,

Based on the description, create the relationship between two tables.

Then, try using the following formula to filter.

[Email] = USERPRINCIPALNAME() &&
(
    [Entity] = "ALL" || [Entity] = RELATED('FactTable'[Entity])
) &&
(
    [Target Segment] = "ALL" || [Target Segment] = RELATED('FactTable'[Target Segment])
) &&
(
    [Survey Type] = "ALL" || [Survey Type] = RELATED('FactTable'[Survey Type])
) &&
(
    [Equipe] = "ALL" || [Equipe] = RELATED('FactTable'[Equipe])
)

You can also view the following documents to learn more information.

Configure row-level security with the dynamic method - Training | Microsoft Learn

Solved: RLS multiple combination - Microsoft Fabric Community

Row-level security (RLS) with Power BI - Microsoft Fabric | Microsoft Learn

Best Regards,

Wisdom Wu

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

[adrianPadurariu]

Thank you @v-jiewu-msft I forgot to mention that in my fact table I don't have the Email column so I am not sure how I can connect the two tables.  Based on your description do I need to make multiple inactive relationships for every column?

Where I have "All," there will be blank cells. I wrote "All" just for better understanding.