RLS on DirectQuery

[gdps_vc]

Greetings everyone!

I'm having trouble configuring the RLS for my report. I've searched several sources on youtube, but nothing has been able to help me.

At first I have 2 tables:

And I configured the RLS as shown in the image below:

When I use EmailBP from BPs por Entidade table in a slicer, I can filter PEOPLE VW_FACT_INDI table normally. However, when I simulate the role in RLS, BPs por Entidade is filtered, but PEOPLE VW_FACT_INDI is not.

Why is that? How can I solve this problem?

Best Regards,

[Anonymous · Accepted solution]

Hi,lbendlin ,thanks for your concern about this issue.

Your answer is excellent!
And I would like to share some additional solutions below.
Hello,@gdps_vc .I am glad to help you.
I noticed that your table relationships have a relationship join status of limited relationship, and the data join mode is Direct Query, which is not conducive to the proper functioning of RLS.
Limited relationship: A limited relationship is a relationship in Power BI that does not create a data structure, but instead resolves row table joins at query time. Such relationships typically use the INNER JOIN syntax and do not add blank values or unrelated rows.
Force Real-Time RLS: Row-level security (RLS) is used to restrict user access to data. Enforcing real-time RLS means applying RLS rules to real-time joins, which may affect the topology of the model.
Topology restriction: topology restriction may occur when finite relations are used in combination with forced real-time RLS. This means that the structure of the model may be restricted, resulting in certain features not working properly. For example, it is not possible to use the RELATED DAX function to retrieve columns with “multiple” ends.

Note that in DirectQuery connection mode, power BI will directly convert the filter criteria to the query data of the corresponding data source, but unfortunately it is not possible to pass the relevant function in Power BI, which indicates that the data in DirctQuery connection mode cannot be used with the USERPRINCIPALNAME() function. Implementing dynamic RLS (I think this is the most important possible reason)
In this case, the best thing to do is to set up the RLS directly and locally in the data source where the DIrectQuery connection is required, instead of setting up the dynamic RLS in Power BI Desktop
You can see that USERPRINCIPALNAME() doesn't work in DirectQuery's RLS because it doesn't work correctly as a query statement passed to the data source.

URL:
Solved: RLS on database level with Direct Query - Microsoft Fabric Community

It should show data related to VM0\admin01, but it doesn't actually return any data.

Here's the Model where I created the Direct Query connection pattern (the relationship is not limited ):.

2. Avoid having many-to-many relationships and two-way filtering directly in the model where you are setting up the RLS.
As lbendlin suggests, when you are setting up RLS, make sure that the relationships are active and avoid using two-way filtered many-to-many relationships.

Try creating bridged tables between tables with many-to-many connections for transitioning many-to-many relationships.

Here are the steps
Create bridged tables: Bridged tables are used to store related entities. For example, if you have two tables “Customer” and “Order”, you can create a bridged table “CustomerOrder”, which contains “CustomerID “and “Order ID”.
Create one-to-many relationship: Create one-to-many relationship between the three tables. Make sure that the relationship between the bridged table and the other two tables is bidirectional so that the filters can be propagated correctly.

Configure RLS: Apply RLS filters on the dimension table, not on the fact table. Make sure the RLS rules are applied correctly.
I hope you find the article links below helpful:

URL:
Row-level security (RLS) guidance in Power BI Desktop - Power BI | Microsoft Learn
Bi-directional relationship guidance - Power BI | Microsoft Learn
DirectQuery model guidance in Power BI Desktop - Power BI | Microsoft Learn

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Carson Jian,
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[lbendlin]

Your RLS needs to be able to control downstream tables.  The relationship you show is bidirectional which basically renders RLS ineffective.  Change to single direction.

[gdps_vc]

I've already tried this possibility, but it didn't work either.

[lbendlin]

How did it not work?

[gdps_vc]

Nothing has changed. When I use EmailBP from BPs por Entidade table in a slicer, I still can filter PEOPLE VW_FACT_INDI table normally.

However, when I test the role in RLS, BPs por Entidade is filtered, but PEOPLE VW_FACT_INDI is not.

[lbendlin]

Follow the arrows.  Are your tables with RLS rules pointing to the fact tables in a single direction all the way through?