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
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.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Urdu Hindi D365 Bootcamp 768x460.png

Urdu Hindi D365 Bootcamp

Dont miss our very own April Dunnam’s The Developer Guide to the Galaxy! Find out what the Power Platform has to offer for the traditional developer.

Top Solution Authors