Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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]
)
Check out the July 2025 Power BI update to learn about new features.
User | Count |
---|---|
6 | |
6 | |
3 | |
2 | |
2 |
User | Count |
---|---|
5 | |
5 | |
4 | |
4 | |
3 |