Row level Security multiple columns Dynamic

[Negi]

Hi All,

I have 1 fact Table which contains approx 100 million rows of data. sample data as below. 

 After this I have created multiple tables which contains useremail id based on there user access and that infomation comes from Global ID, Region ID, Country ID, City ID columns. and all these seperate tables contains duplicate columns as well.

After created this tables I have make relationship between these tables based on respective ID columns but issue is that Global ID, Global ID, Region ID, Country ID, City ID contains duplicate ID as well since for 1 ID multiple users can have access also any user name can me mentioned in multiple ID tables as well. 

Please suggest how to implement RLS in this case.

Global ID	Region ID	Country ID	City ID	Date	Cost	Commission
Global	Region 1	Country 1	City 1	1-Jan-25	546	65
Global	Region 1	Country 1	City 1	2-Jan-25	435	65
Global	Region 1	Country 2	City 2	3-Jan-25	66	65
Global	Region 1	Country 2	City 3	4-Jan-25	543	7657
Global	Region 2	Country 3	City 4	5-Jan-25	546	65
Global	Region 2	Country 3	City 4	6-Jan-25	435	65
Global	Region 2	Country 3	City 5	7-Jan-25	66	65
Global	Region 2	Country 3	City 6	8-Jan-25	543	7657
Global	Region 3	Country 4	City 7	9-Jan-25	546	65
Global	Region 3	Country 5	City 8	10-Jan-25	435	65
Global	Region 3	Country 6	City 9	11-Jan-25	66	65
Global	Region 3	Country 7	City 10	12-Jan-25	543	7657

Other tables are as mentioned below :-

Global Users 

Global ID	UserEmail
Global	User 1 email
Global	User 2 email
Global	User 3 email
Global	User 4 email
Global	User 5 email

Region ID 

Region ID	UserEmail
Region 1	User 6 email
Region 2	User 7 email
Region 3	User 8 email
Region 1	User 9 email
Region 2	User 10 email
Region 3	User 11 email
Region 1	User 12 email
Region 2	User 13 email
Region 3	User 14 email

Country ID

Country ID	UserEmail
Country 1	User 15 email
Country 2	User 16 email
Country 3	User 17 email
Country 4	User 18 email
Country 5	User 19 email
Country 6	User 20 email
Country 7	User 21 email
Country 1	User 22 email
Country 2	User 23 email
Country 3	User 24 email
Country 4	User 25 email
Country 5	User 26 email
Country 6	User 27 email
Country 7	User 28 email
Country 7	User 10 email
Country 6	User 6 email

City ID

City ID	UserEmail
City 1	User 29 email
City 2	User 30 email
City 3	User 31 email
City 4	User 32 email
City 5	User 33 email
City 6	User 34 email
City 7	User 35 email
City 8	User 36 email
City 9	User 37 email
City 10	User 38 email
City 1	User 39 email
City 2	User 40 email
City 3	User 41 email
City 4	User 42 email
City 5	User 43 email
City 6	User 44 email
City 7	User 45 email
City 8	User 46 email
City 9	User 47 email
City 10	User 48 email
City 9	User 14 email
City 8	User 26 email

[rohit1991 · Accepted solution]

Hi @Negi 

You can secure this scenario by building a mapping/security table where each row links a user to their allowed Region, Country, and City. Relating this table to your fact data makes RLS much easier to manage. Then, apply a single filter in your RLS role like:

[UserEmail] = USERPRINCIPALNAME()

This ensures that each user only sees the rows matching their mapped access. A few points to keep in mind:

• This approach avoids hardcoding filters for every column.

• It’s easy to extend when new users, regions, or cities are added — just update the mapping table.

• Use composite keys if you need to control access across multiple levels (e.g., Region + Country).

• You can manage the mapping table in OneLake or SQL so that admins update permissions without editing the RLS role.

• Test with different sample users to confirm that filtering cascades properly through all columns.

[Negi · Accepted solution]

Hi @rohit1991 ,

This function I can use in RLS role. but I am not able to understand , If I create a single table. for example it contains ID column and user email id where each row contains email id. than how I will make a relation which with new Mapping table with financial table since financial table will contains those information in multiple columns and in new mapping sheet it will contains those respective id in single column name id ?

if it's possible with any dynamic dax then pls share once.  ?

[v-sgandrathi]

Hi @Negi,

Thank you @rohit1991 for your response.

Has your issue been resolved?
If the response provided by the community member addressed your query, could you please confirm? It helps us ensure that the solutions provided are effective and beneficial for everyone.

Thank you for your understanding!

[v-sgandrathi]

Hi @Negi,

I wanted to follow up on our previous suggestions regarding the issue. We would like to hear back from you to ensure we can assist you further.

Please let us know if there’s anything else we can do to help.

Thank you.

[v-sgandrathi]

Hi @Negi,

Just wanted to follow up and confirm that everything has been going well on this. Please let me know if there’s anything from our end.
Please feel free to reach out Microsoft fabric community forum.

Thank you.

[rohit1991 · Accepted solution]

Hi @Negi 

You can secure this scenario by building a mapping/security table where each row links a user to their allowed Region, Country, and City. Relating this table to your fact data makes RLS much easier to manage. Then, apply a single filter in your RLS role like:

[UserEmail] = USERPRINCIPALNAME()

This ensures that each user only sees the rows matching their mapped access. A few points to keep in mind:

• This approach avoids hardcoding filters for every column.

• It’s easy to extend when new users, regions, or cities are added — just update the mapping table.

• Use composite keys if you need to control access across multiple levels (e.g., Region + Country).

• You can manage the mapping table in OneLake or SQL so that admins update permissions without editing the RLS role.

• Test with different sample users to confirm that filtering cascades properly through all columns.

[Negi · Accepted solution]

Hi @rohit1991 ,

This function I can use in RLS role. but I am not able to understand , If I create a single table. for example it contains ID column and user email id where each row contains email id. than how I will make a relation which with new Mapping table with financial table since financial table will contains those information in multiple columns and in new mapping sheet it will contains those respective id in single column name id ?

if it's possible with any dynamic dax then pls share once.  ?

[rohit1991 · Accepted solution]

Hi @Negi 

This can be solved by creating a single mapping table with columns like UserEmail, RegionID, CountryID, and CityID, where each user can have multiple rows if they need access to more than one level (for example Region + City). Connect this mapping table to your fact table and set the RLS rule as [UserEmail] = USERPRINCIPALNAME(). This way Power BI will automatically filter the data based on the mapping, and each user will only see the rows linked to their entries without needing separate RLS rules for every column.