topic Re: Dynamic RLS and using DirectQuery in DAX Commands and Tips

Dynamic RLS and using DirectQuery

NielsBakker — Fri, 18 Jul 2025 11:47:27 GMT

speedramps again thank you very much for your help. Really appreciate your time and help for trying to solve this issue. I hope I've explained my issue as clear as possible.

Goal

Set up flexible (dynamic) Row-Level Security (RLS) in Power BI using a UserSecurity table that filters a FactTable.
When a user logs in, the UserSecurity table should filter to that user, which in turn should filter the FactTable to only show data the user is allowed to see.

Requirements

All tables in the data model use DirectQuery.
The UserSecurity table must support wildcards to indicate full access, in this case I use 0. The reaon for this is that I want to avoid that the table will get extremely large.
- Example: If a user has access to all ResourceGroups within a Subscription, the ResourceGroup field should be 0.

A key use case to support is (please see attached Excel file (UserSecurity) for the full overview:
- User 16 has access to:
  - All ResourceGroups in Subscription 17
  - Only ResourceGroup 15348 in Subscription 25

Limitations

Because I’m using DirectQuery, I cannot use functions like CALCULATE or COUNTROWS in Manage Roles for RLS (they are disabled).
If a relationship between the tables is needed, a many-to-many relationship is needed between UserSecurity and the FactTable because:
- Multiple users in UserSecurity
- Each user can have multiple different access rules (some full access via wildcard, some partial)
- However, many-to-many relationships are problematic with DirectQuery

What I've Tried

It works perfectly in Import Mode (use Test 1 as Role), but I must use DirectQuery. Please see attached PBI how it should work.
I’m stuck translating the logic into a DAX approach that works in DirectQuery mode with these constraints.

Ask
How can I implement this kind of dynamic RLS with wildcard support in DirectQuery mode?

I've attached 2 Excel files and a PowerBI file that can be used. Sorry, I don't know how to share a DirectQuery model because of privacy concerns. I hope this Excel files gives enough context.

Link to download files: https://we.tl/t-PONAaflP8Q

Many thanks again, and appreciate the help!

Kind regards,

Niels

Re: Dynamic RLS and using DirectQuery

FBergamaschi — Fri, 18 Jul 2025 13:28:57 GMT

Hi,

limitations are due to the need to translate in SQL complex DAX statements.

the CALCULATE and COUNTROWS calculations you need to perform are working on facts/dimensions/bridges or on the security table? I am asking because you might consider (maybe) to import only the security table if the CALCULATE and COUNTROWS calculations you need to perform and on the security table

Othwerwise either you import (not an option, if I understand correclty) or you write an old style DAX, that will be painful though

If this helped, please consider giving kudos and mark as a solution

me in replies or I'll lose your thread

consider voting this Power BI idea

Francesco Bergamaschi

MBA, M.Eng, M.Econ, Professor of BI

Re: Dynamic RLS and using DirectQuery

NielsBakker — Fri, 18 Jul 2025 13:44:54 GMT

FBergamaschi thanks for your response!

I've tried to import only the security table, and use Direct Query for the FactTable. But in Manage Roles, the DAX is still referencing to the DirectQuery table and therefore, is not allowed to use unfortunately.

But thanks for thinking with me!

Re: Dynamic RLS and using DirectQuery

speedramps — Fri, 18 Jul 2025 14:36:46 GMT

Hi again NielsBakker

This example shows how to implement RLS so a person can be given access to just some or all stores in a country.

The Security table stipulates who has access to which stores in which countries.

The * asterisk denotes that a person has access to all stores in the designated country unless then is an "X" in the block column.

Note that several countries may have identical store names but they are separate stores.

Use the View As function to test Mark, Peter, Sue, Tim, Katy and Jane have the correct restricted access to the Fact table secrets.

I know that you require Direct Query and I will try answer that later.
This solution works for Import Mode, Fabrix Driect Lake and potentially a Composite Mode.

You will need to rename the tables and columns names for your Resources Groups and Subscriptions.
You will also need to have the * and X to your I and 0.

Click here to download PBIX from OneDrive

Note that Security and Fact table have a Many to Many relation with Both cross filter direction.

This will generate a red triangle warning, which you can ignore because it is ok in this scenario.

In Manage Roles the Securiry table has a RLS (Row Lecve; Security) Filter for the login username

Whereas the Fact table uses DAX to determin the RLS Filter

Here is the DAX RLS filter logic

// create a temp table allowed stores for the User and Country VAR allow = CALCULATETABLE( VALUES(Security[Store]), Security[User] = USERNAME(), Security[Block] <> "X" ) // create a temp table prevented stores for the User and Country VAR prevent = CALCULATETABLE( VALUES(Security[Store]), Security[User] = USERNAME(), Security[Block] = "X" ) RETURN // Allow the user access if the country store or * is in the allow temp table. // But prevent acceess if that user country store is blocked. ( [Store] IN allow || "*" IN allow ) && NOT [Store] IN prevent

Test by using View as Jane who has access to

All store in France
Just stores 1 and 4 in the UK
And all stores in USA except 1 and 3

Works perfectly.

I like how how this solution is easily to understand, develop, test and maintain.

So you can easily make changes to the security config table as when staff need access adding or removing.

Re: Dynamic RLS and using DirectQuery

speedramps — Fri, 18 Jul 2025 14:51:55 GMT

HI NielsBakker

If you MUST use direct query then consider implementing the row level security inside the source system rather than Power BI.

For example if the source is SQL Server, MY SQL or Oracle then implement the security in there rather than Power BI.

The data base administator will help you to that, or you can learn how via the SQL Server, MY SQL or Oracle comuinity forums.

Alternatively consider Power BI Import Mode, a composite model or Fabric Direct Lake so that you can leverage all DAX comands inside Power BI RLS (Row Level Security).

Sorry, this is not the answer you want, but one of the reasons that Fabric Direct Lake is becoming so populare is that it is ideal for semantic models with RLS regardless of the data source.

Learn about Fabric Direct Lake here

https://learn.microsoft.com/en-us/fabric/fundamentals/direct-lake-overview

Re: Dynamic RLS and using DirectQuery

speedramps — Fri, 18 Jul 2025 15:13:43 GMT

Hi again NielsBakker

This users has posted some good videos about RLS with Direct Query that you might find interesting

Re: Dynamic RLS and using DirectQuery

v-hashadapu — Mon, 21 Jul 2025 10:46:50 GMT

Hi NielsBakker , hope you are doing great. The answers from speedramps seems appropriate. May we know if your issue is solved or if you are still experiencing difficulties. Please share the details as it will help the community, especially others with similar issues.

Re: Dynamic RLS and using DirectQuery

NielsBakker — Mon, 21 Jul 2025 13:54:40 GMT

speedramps thank you very much for your help! Really appreciated it and you were very helpful. In the end to make it work I used the following:

- Imported the user security table with wildcards

- Imported a bridge table with all columns that are in the security table except for userID. All possible combinations are in 1 time and I added a unique column (concatenated all columns). Used many-to-many relationship and used the DAX as you explained.

- FactTable remains DirectQuery, but in here I also added the same concatenated column and connected it with the BridgeTable. Relationship is One-To-Many so that when the Bridge table gets filtered, it automatically filters the factTable.

Again, thanks a lot!

Re: Dynamic RLS and using DirectQuery

speedramps — Tue, 22 Jul 2025 08:48:13 GMT

NielsBakker thank you for letting us know the outcome.

The "composite" solution you selected is a common method.

Security rules rarely change, so the security table can be imported.

But sometimes direct query is needed for the Fact table if it is being constanly updated and the most recent data is required.

However, Fabric Direct Lake semantic data models now allow near real time replication.

Allowing both tables to have the most recent data like Direct Query but all the DAX functionality like import model.

If you scale up your data then Fabric Direct Lake will be worth considering.

Learn more abour Fabric here
https://learn.microsoft.com/en-us/fabric/fundamentals/direct-lake-overview