cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
cmcd21
Frequent Visitor

Dynamic Row Level Security - Bridge Table

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.

 

UserIDContinentCountryCity
1EuropeNULLNULL
1AsiaThailandBangkok
2AsiaChinaBeijing
2AsiaThailandBangkok
2AfricaSouth AfricaCape 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

1 REPLY 1
cmcd21
Frequent Visitor

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]
)

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

Get Ready for Power BI Dev Camp

Power BI Dev Camp - September 30th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!

PowerPlatform 768x460.png

Microsoft Learn

Check out our new Discover Your Career Path blog post series and get all the details.

Top Kudoed Authors