Using table like below to create row level security

[GAPER]

I have a mapping table like this. How do I write a formula to filter projects only show = 1 for each individual?

I was thinking using userprincipalname() but it's a measure so it' cant be used as a filter. Thanks!

Email	Project	Show
person1@abc.com	Project 1	1
person1@abc.com	Project 2	1
person1@abc.com	Project 3	0
person1@abc.com	Project 4	0
person2@abc.com	Project 1	1
person2@abc.com	Project 2	1
person2@abc.com	Project 3	1
person2@abc.com	Project 4	1

Clafirication: this is just an extra filter table. I have an underlying data table for example

Project Revenue 

Project 1 100

Project 2  200

[Anonymous · Accepted solution]

Hi @GAPER ,

Thanks @FarhanAhmed  for sharing, here are some suggestions that I hope will be helpful to you.
You can write the formula when creating the role:

[Email] == USERPRINCIPALNAME() && [Show] == 1

The formula uses this function to dynamically filter items based on the logged in user's email and the "show" column in the mapping table.
Roles can be tested using views as, which allows you to simulate the experience of a user assigned this role.

For more information about RLS, please refer to the document: Row-level security (RLS) with Power BI - Power BI | Microsoft Learn.

If your Current Period does not refer to this, please clarify in a follow-up reply.

Best Regards,

Clara Gong

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

[GAPER]

this is the extra layer of information i used to filter the table. Not the underlying table

[FarhanAhmed]

Why can't you filter out Show=0 from your data source or in Power Query?