Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Direct query row level security cross filter issue on single table

Hi

 

I'm attempting to apply row level security in a composite PowerBI model via DAX using a disconnected table containing the security information of all users.

 

Security table is called 'SecurityModel' and contains 2 columns, Userprincipalname and UserRegion.

 

On the tables in my model that have been imported (i.e. not direct query), the DAX I'm using below is working perfectly to secure the model  ('Region' is a column in fact table that security is being applied to):

 

[Region] IN

selectcolumns(
filter(

SUMMARIZE(SecurityModel, 'SecurityModel'[UserRegion],SecurityModel[userprincipalname]),
SecurityModel[userprincipalname]=userprincipalname()

),
"UserRegion",'SecurityModel'[UserRegion]

)

 

However, on a single direct query fact table with no joins to any other tables I'm getting the following error:

 

ErrorDW.PNG

 

 

 

As I'm applying the filter to a single direct query table with no joins to any other tables I don't understand why I'm getting this error message. 

 

The only thing I can see from other posts is that there was previously a preview feature to enable or disable cross filtering in direct query, see below. 

 

qwe.PNG

 

However, this is not an available option in the December 2018 release and there are no other options I can see to turn this on and off for my single direct query table.

 

If anyone has any ideas or experience of this that they could share it would be really appreciated. Thanks.

 

7 REPLIES 7
AnonymousPerson
Advocate V
Advocate V

Once again re-confirmed in September 2021 that you cannot do anything to filter a dynamic RLS role using anything other than a straight "equal to" comparison.

Kind of a bummer, but it would probably totally murder performance to do anything otherwise anyway.

mubp
Frequent Visitor

I would like to receive any update about this subject matter. I have no issues on setting RLS on a file where all table are Direct Query  but when creating the same schema but with a composite model (some import tables and other DQ) the RLS is not working.

 

jeffrey_wang
Power BI Team
Power BI Team

The misleading error message and a hidden product crash dump are a product defect that will be fixed in a future release. But the bug fix won't enable this modeling approach. For any DirectQuery table, be it in a composite model or in a pure DirectQuery model, RLS expressions only allow intra-row pure scalar functions. Measures and iteration functions like FILTER or COUNTROWS are not supported.

Anonymous
Not applicable

Thanks for the reply. Good to know it is a bug.

 

Are there any known DAX patterns for applying dynamic row level security on direct query tables that could be shared?

 

The only way I (sort of) got this to work was by connecting my single direct query table to an imported table containing my security data in and then applying the security on the imported table. However, this was so inefficient in terms of the SQL being passed back to the database via the connector that it was a non-starter.

 

With a complex many-to-many security model, trying to push this down into the underlying database tables is not really viable.

 

Any other advice help on how to get a dynamic security model working with one direct query table would be excellent. So close to utopia... 🙂

 

Thanks

If you can define RLS on a composite model, why can't you create all the necessary tables in the relational database so RLS rules and joins are pushed to SQL? Are you running SQL Server? Does Kapser's pattern https://www.kasperonbi.com/power-bi-desktop-dynamic-security-cheat-sheet/ apply to your scenario?

v-shex-msft
Community Support
Community Support

Hi @Anonymous,

 

In fact, I also not found any document mentioned this. Normally preview features will be remove when it update to stable version or rollback when it caused some issues.(compatibility or other issues)

 

You can download the last version of power bi desktop, if it works it means feature has been updated to stable version.

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
Anonymous
Not applicable

Hi Xiaoxin 

 

Appreciate the reply. I tried updating to the latest version today and still can't see this preview feature and am still getting the same error when applying the DAX to the single direct query table. 

 

If this feature was previously selected in preview and you then update Power BI would this remain selected in the background by default? Even though the option to select this preview feature has disappeared.

 

If you had any more info on what happened to this preview feature that would be really helpful.

 

Thanks

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.