Dynamic Row Level Security - Bridge Table

Anonymous — Sun, 02 Feb 2020 15:59:47 GMT

Hello,

I have been watching videos and reading all the blog posts I can about Dynamic Row Level Security in Power BI but I cannot get an answer to my problem.

Problem:

I have the following bridge table in my data model. It is joined to the Orders table on City. It is also joined to a UserList table (which contains the users email address) on UserID.

UserID	Continent	Country	City
1	Europe	NULL	NULL
1	Asia	Thailand	Bangkok
2	Asia	China	Beijing
2	Asia	Thailand	Bangkok
2	Africa	South Africa	Cape Town

User 1 should see data from the Orders table for Bangkok and all cities in Europe.

User 2 should only see data from the Orders table for Beijing, Bangkok and Cape Town.

The following DAX script works correctly for filtering the Orders table for User 2 but only shows Bangkok for User 1. I have entered it under the Orders table in the Manage Roles window.

[SalesCity] =
LOOKUPVALUE(
'UserOrderBridge'[City],
'UserList'[UserName],UserPrincipalName(),
'UserOrderBridge'[City],
Orders[SalesCity]
)

So my issue is how do I change this script to pull back all of Europe and Bangkok for User 1?

I tried to use a IF(ISBLANK() statement but that didn't work.

If i create another Role for Continent, itll bring back all the data for Asia and africa for User2.

I am pretty new to DAX so any help is greatly appreciated.

Thanks

Re: Dynamic Row Level Security - Bridge Table

Anonymous — Mon, 03 Feb 2020 11:13:11 GMT

I may be close to a solution but require some help with the DAX.

I am have created two Security Roles: City and Continent and I have added in a Level column to my bridge table.

I want to filter the DAX LOOKUPVALUE script above to only look at the rows where LEVEL = "City". e.g. something like below

[SalesCity] =
LOOKUPVALUE(
'UserOrderBridge'[City] WHERE LEVEL= "City",
'UserList'[UserName],UserPrincipalName(),
'UserOrderBridge'[City] WHERE LEVEL= "City",
Orders[SalesCity]
)

topic Re: Dynamic Row Level Security - Bridge Table in Developer

Dynamic Row Level Security - Bridge Table

Re: Dynamic Row Level Security - Bridge Table