Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin 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.
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
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]
)
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
8 | |
6 | |
5 | |
2 | |
2 |
User | Count |
---|---|
4 | |
4 | |
3 | |
3 | |
2 |