how should I protect data when RLS is not an option?

[CBO2404]

I have two tables, one for comments in a report (Fact_reports) and one for access permission (Fact_access).

A report can always be viewed, except when a select number of users are listed in the access table.

For example, report id 1 is not in the Fact_access table, so everyone can view the information. However, report id 2 is in the Fact_access table and can only be viewed by users in the access table. I cannot use RLS because not all report rules are in the Fact_access table. How can I display (or count) in a Power BI visual only the comments that I have access to? 
As user 1 or user2 I can see both comments but user3 may only see comments of id 1.
This is a simple example of my problem. Hope the question is clear.

[Anonymous · Accepted solution]

Hi @CBO2404 ,

I think you can do these steps below:

1. Create a relationship between the Fact_Reports and Fact_Access tables based on the reportid column.

2. You can create two measures.

HasAccess = 
VAR CurrentUser = USERPRINCIPALNAME()
VAR ReportID = SELECTEDVALUE(Fact_Reports[reportid])
RETURN
IF(
    ISBLANK(LOOKUPVALUE(Fact_Access[userprinciple], Fact_Access[reportid], ReportID, Fact_Access[userprinciple], CurrentUser)),
    TRUE(),
    FALSE()
)

VisibleComments = 
IF(
    [HasAccess],
    COUNTROWS(Fact_Reports),
    BLANK()
)

3. Use the measure in your visual to filter the comments. You can add the VisibleComments measure to your visual to ensure that only the comments the user has access to are displayed.

Best Regards

Yilong Zhou

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