topic Re: LOOKUPVALUE overrides RLS in DAX Commands and Tips

LOOKUPVALUE overrides RLS

montoyesco — Wed, 01 Jul 2020 20:44:49 GMT

Hi,

I have two tables:

ContactOrigin:

ContactDestiny:

Both tables are not related inside the model (even though they have the "Id Origin" in common). In Power BI, both tables have RLS applied on Company Id Origin and Company Id Destiny, respectively. So, when I apply RLS for the company with Id 80022233 (BBB Ltda), both tables are filtered and that's ok:

But what I need is the "Id Origin" field in Destiny Contact Table shows only values that belong to BBB Ltda in Contact Origin Table (in this case, "567890" doesn't belong to BBB Ltda in Origin Contact table). I tried to create a calculated column to solve this:

Id Origin Lookupvalue = LOOKUPVALUE( ContactOrigin[Id Origin]; ContactOrigin[Id Origin]; ContactDestiny[Id Origin] )

But the outcom is the same, which means that LOOKUPVALUE overrides the RLS security:

Is there another way to solve this issue? This is what I expected to see:

Thanks!

Re: LOOKUPVALUE overrides RLS

lbendlin — Wed, 01 Jul 2020 17:48:24 GMT

If you don't have an active relationship how is RLS supposed to know that it needs to filter the other table? In that case you need to apply the RLS rules to BOTH tables.

Re: LOOKUPVALUE overrides RLS

montoyesco — Wed, 01 Jul 2020 18:28:15 GMT

I put this case as simple as I can. In my real model, these two tables doesn't exists. In the real one, I have two tables which are filtered with RLS, but I need both unrelated. Maybe the following link will explain you this case better than me:

https://community.powerbi.com/t5/Desktop/Lookupvalue-override-RLS/td-p/579361

As I say, I just try to "simplified" the case. In the link above it's explained, but in essence, it is the same issue.

Re: LOOKUPVALUE overrides RLS

lbendlin — Wed, 01 Jul 2020 19:33:42 GMT

Answer is still the same. If the tables are unrelated then you need to apply the RLS rules twice, once for each table.

Re: LOOKUPVALUE overrides RLS

montoyesco — Sun, 05 Jul 2020 17:47:00 GMT

So.... Don't you have the answer to this problem?.... anyone else? please?