Implementing Row-Level Security (RLS) with Email

[PatipolLenpok]

The first thing I am trying to do is set up Row-Level Security (RLS) for viewing qcrm_opportunity data using Email for RLS. The email information is stored in qcrm_employee.

However, I encountered an issue with the relationship between qcrm_employee and qcrm_opportunity. Since I am using DirectQuery with a large number of tables, I am unable to set "Make this relationship active" to Yes. I suspect this is due to a circular relationship in the data model, which looks something like this:

qcrm_employee → qcrm_opportunity → qcrm_team → qcrm_employee

When I tried modifying the relationships, it made the data even more complex and confusing.

As an alternative, I considered using Manage Roles to filter data based on the user’s email. My approach is to retrieve the Employee ID from qcrm_employee by matching the user's email, and then compare it to qcrm_opportunity[qcrm_salesresponsible].

The DAX expression I am using is:
qcrm_opportunity[qcrm_salesresponsible] =
LOOKUPVALUE(qcrm_employee[Employee], qcrm_employee[Email], USERPRINCIPALNAME())

-EMP is From qcrm_employee[Employee] -OPP From qcrm_opportuntity

Based on my observation, the data appears as blank, except for Closed Won.

[Anonymous]

Hi @PatipolLenpok 

Thank you grazitti_sapna for your wonderful response to this post.

May I ask if your problem has been resolved? If so, please consider accepting grazitti_sapna's reply as a solution, which will allow other users experiencing the same problem to find a solution more quickly.

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

[grazitti_sapna]

Hi @PatipolLenpok,

This can be due to inactive relationship between the tables, Also Lookupvalue doesn't work with direct query effectively, you can alternatively use a bridge table to combine both the tables and try to implement RLS on bridge table instead or you can try the DAX below,

qcrm_opportunity[qcrm_salesresponsible] IN
FILTER(
qcrm_employee,
qcrm_employee[Email] = USERPRINCIPALNAME()
)

🌟 I hope this solution helps you unlock your Power BI potential! If you found it helpful, click 'Mark as Solution' to guide others toward the answers they need.
💡 Love the effort? Drop the kudos! Your appreciation fuels community spirit and innovation.
🎖 As a proud SuperUser and Microsoft Partner, we’re here to empower your data journey and the Power BI Community at large.
🔗 Curious to explore more? [Discover here].
Let’s keep building smarter solutions together!

[PatipolLenpok]

I tried many codes and always got an error like this. @grazitti_sapna 

[grazitti_sapna]

Hi @PatipolLenpok,

This might be because FILTER () returns a table, and IN requires a single-column table.

Try this DAX instead 

qcrm_opportunity[qcrm_salesresponsible] IN
SELECTCOLUMNS(
FILTER(qcrm_employee, qcrm_employee[Email] = USERPRINCIPALNAME()),
"Salesperson", qcrm_employee[Email]
)

🌟 I hope this solution helps you unlock your Power BI potential! If you found it helpful, click 'Mark as Solution' to guide others toward the answers they need.
💡 Love the effort? Drop the kudos! Your appreciation fuels community spirit and innovation.
🎖 As a proud SuperUser and Microsoft Partner, we’re here to empower your data journey and the Power BI Community at large.
🔗 Curious to explore more? [Discover here].
Let’s keep building smarter solutions together!