Dynamic RLS and using DirectQuery

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.