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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
asm495
Regular Visitor

Row Level Security - Multiple Filter Criteria

I am trying to use RLS to filter some data according to a table which stores users territory information. The territory information is a table ("UserTerritories") consisting of both a Country and a Territory. Both of these fields can be wildcarded with a * to indicate they have access to either all countries or all territories within a country.

 

Example "UserTerritories" information table shown here. This would indicate that the first user (User 100) has access to all of Germany plus the West and North territories in Spain. User 200 has access to the East and Paris territories in France. User 300 has access to all data.

 

UsernameCountryTerritory
100Germany*
100SpainWest
100SpainNorth
200FranceEast
200FranceParis
300**

 

Example "Account" data to be filtered using RLS:

AccountCountryTerritory
A001GermanyWest
A002GermanyBerlin
A003SpainWest
A004SpainNorth
A005SpainSouth
A006FranceNorth
A007FranceEast
A008FranceWest
A009FranceParis

 

I have been able to use SELECTCOLUMNS in my RLS rules (as shown below) to be able to filter either country or territory but this treats each of these fields individually rather than as a combination. What this means is that if a user's territory rules has a * for one of the territory fields then it would include all territories for any of the countries they have access to and not just the one specific country.

 

var UserCountrySet = SELECTCOLUMNS(
  FILTER(
    'UserTerritories',
    [Username] = USERPRINCIPALNAME()
  ),
  "Country", [Country]
)

var UserTerritorySet = SELECTCOLUMNS(
  FILTER(
    'UserTerritories',
    [Username] = USERPRINCIPALNAME()
  ),
  "Territory", [Territory]
)

return
  ("*" IN UserCountrySet || [Country] IN UserCountrySet) &&
  ("*" IN UserTerritorySet || [Territory] IN UserTerritorySet)

 

 

Can anyone provide any guidance how I can filter the list of Accounts by seeing if it matches ANY of the row entries (i.e. the Country & Territory combination) in my "UserTerritories" table please? Any help greatly appreciated.

 

Thanks!

 

1 ACCEPTED SOLUTION
OwenAuger
Super User
Super User

Hi @asm495 

 

There are different ways of writing the condition.

I would recommend something like this (to be placed as a RLS filter on the Account table):

 

VAR CurrentUser =
    USERPRINCIPALNAME ()

VAR CurrentCountryTerritory =
    -- Construct 4 row table containing the 4 combinations:
    -- ( Country, Territory ), ( Country, * ), ( *, Territory ), ( *, * )
    VAR CurrentCountry = 
        SELECTCOLUMNS ( { "*", Account[Country] }, "@Country",[Value] )
    VAR CurrentTerritory =
        SELECTCOLUMNS ( { "*", Account[Territory] }, "@Territory",[Value] )
    RETURN
        CROSSJOIN ( CurrentCountry, CurrentTerritory )


VAR Include =
    -- This returns TRUE only if at least one of the combinations in the variable CurrentCountryTerritory
    -- appears in the 'UserTerritories' table for the current user
    CALCULATE (
        NOT ISEMPTY ( 'UserTerritories' ),
        TREATAS ( CurrentCountryTerritory, 'UserTerritories'[Country], 'UserTerritories'[Territory] ),
        'UserTerritories'[Username] = CurrentUser
    )

RETURN
    Include

The logic is to:

  1. For each row of Account table, there are four possible Country/Territory combinations that could exist in 'UserTerritories' that would allow that row of Account to be vislble.
  2. Those four combinations are:
    1. ( Country, Territory )
    2. ( Country, * )
    3. ( *, Territory )
    4. ( *, * )
  3. If any of those is present in 'UserTerritories' when filtered on the current user, then return TRUE, i.e. the current row of Account will be visible.

Does this work for you?

 

Regards,

Owen

 


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

View solution in original post

2 REPLIES 2
OwenAuger
Super User
Super User

Hi @asm495 

 

There are different ways of writing the condition.

I would recommend something like this (to be placed as a RLS filter on the Account table):

 

VAR CurrentUser =
    USERPRINCIPALNAME ()

VAR CurrentCountryTerritory =
    -- Construct 4 row table containing the 4 combinations:
    -- ( Country, Territory ), ( Country, * ), ( *, Territory ), ( *, * )
    VAR CurrentCountry = 
        SELECTCOLUMNS ( { "*", Account[Country] }, "@Country",[Value] )
    VAR CurrentTerritory =
        SELECTCOLUMNS ( { "*", Account[Territory] }, "@Territory",[Value] )
    RETURN
        CROSSJOIN ( CurrentCountry, CurrentTerritory )


VAR Include =
    -- This returns TRUE only if at least one of the combinations in the variable CurrentCountryTerritory
    -- appears in the 'UserTerritories' table for the current user
    CALCULATE (
        NOT ISEMPTY ( 'UserTerritories' ),
        TREATAS ( CurrentCountryTerritory, 'UserTerritories'[Country], 'UserTerritories'[Territory] ),
        'UserTerritories'[Username] = CurrentUser
    )

RETURN
    Include

The logic is to:

  1. For each row of Account table, there are four possible Country/Territory combinations that could exist in 'UserTerritories' that would allow that row of Account to be vislble.
  2. Those four combinations are:
    1. ( Country, Territory )
    2. ( Country, * )
    3. ( *, Territory )
    4. ( *, * )
  3. If any of those is present in 'UserTerritories' when filtered on the current user, then return TRUE, i.e. the current row of Account will be visible.

Does this work for you?

 

Regards,

Owen

 


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

Thank you so much. This was the perfect solution and did exactly what I needed! Very much appreciated.

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Top Solution Authors
Top Kudoed Authors