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.

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

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.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.