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

Join us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.

Reply
Anonymous
Not applicable

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
Anonymous
Not applicable

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
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.