RLS in Direct Query

[Anonymous]

Hi Guys

I created the below expression for RLS and it worked perfectly for my imported tables but when using the same for Direct Query it is giving me an error.

kindly help in sorting this as I'm stuck with this issue.

[CompanyId]=
CALCULATE (
MAX ( Deals[CompanyId] ),
FILTER ( Users, Users[LoginLevelName] = "Owner")
)

Error:

Regards,

Himanshu

[v-kelly-msft]

Hi @Anonymous ,

Based on my test,the RLS rule works in direct query:

My original table is as below:

And my RLS rule is :

And you will see that it works after clicking test as a role:

No error returns.

But if you are having an issue,you could try to separate the rule as below:

Then create a measure :

_Value = CALCULATE(MAX(TEST[Value]),FILTER(ALL(TEST),'TEST'[Country]=SELECTEDVALUE(TEST[Country])))

You will also see:

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[aj1973]

Hi @Anonymous 

Direct Query mode comes with limitations, Including using measures

https://docs.microsoft.com/en-us/power-bi/connect-data/desktop-use-directquery

I recommend using the Power BI desktop feature to implement RLS

https://docs.microsoft.com/en-us/power-bi/admin/service-admin-rls

[Anonymous]

It doesnt solve the issue. 

Is there a way I can change this expression as per the Direct query?

[CompanyId]=
CALCULATE (
MAX ( Deals[CompanyId] ),
FILTER ( Users, Users[LoginLevelName] = "Owner")
)

Regards,

Himanshu

[aj1973]

Nothing wrong with the measure! but CALCULATE is not allowed in RLS

Did you try to implement RLS through the desktop instead of measure? if it didn't work then you might need to change the Mode of connection to Import mode at least for the User Table. But still you will need to use the RLS of the desktop.

[amitchandak]

@Anonymous , check ,if this video from guyinacube can help ?

https://guyinacube.com/2016/06/07/row-level-security-for-cloud-models-and-directquery-in-power-bi/

[Anonymous]

Hi Amit,

Hope you're doing good.

This video doesn't help as I'm doing RLS at Desktop level and the video shows RLS functionality as Service level.

Regards,

Himanshu