weird behavior when using RLS in direct query mode

[hi_world]

Hello, 

For the axample i have two tables : 

Relationship :  Table1 1----N Table2

I have one role : RLS :  [email] = USERPRINCIPALNAME()

I apply the role in the two tables. 

When i display one column from Table2 in visualization i have this sql generated : 

select column1 from

  (Select * from Table2 where Table2.email ='toto@toto.com' ) test

   INNER JOIN Table1 on test.key = Table1.key

   WHERE Table1.email ='toto@toto.com'

I dont want powerbi to do the join with Table1. 

I don't understand this behavior.
How to avoid it?

Best regards

[v-jingzhang]

Hi @hi_world 

The join operation is caused by the relationship between two tables. And you have applied the RLS rule on both tables. Since the relationship exists, usually you only need to apply the RLS rule on one table that can filter the other table via the relationship. Try removing the RLS rule on Table1 or Table2. This may give you a different result. 

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

[hi_world]

Hello @v-jingzhang 

what would be your solution if you still have to apply the same role on both tables because both tables have RLS ? 

Best regards

[v-jingzhang]

Hi @hi_world 

Is it possible to remove the relationship between two tables? 

[hi_world]

No, it is an important relationship 

[hi_world]

Hello @v-jingzhang 

So you tell me that this is a normal behavior of powerbi.
the problem with this join generated by powerbi can cause a loss of information on certain rows of the tables. because of the where on the second table.
Thanks for your feedback