Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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]
)
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
14 | |
2 | |
2 | |
1 | |
1 |