Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
siddhantk989
Helper III
Helper III

How to implement dynamic security while creating multiple reports from same data set?

Hi,

 

 I have a dataset with around 3 million records in it and currently I am using the SQL import feature to get my data, because the data set is huge and I don’t want to set up multiple auto refresh in it what I am doing is that i am creating a single blank report form my desktop Power Bi app and publishing it to the Power Bi website and then from the Power BI website I am creating multiple reports form the dataset.

 

 The problem that I am facing with that is that the reports i am building using the dataset have different kind of row level security for them. For example for 1 report customer A should see all data for New York but for the second report customer A should see all data only for Illinois. I am using dynamic security to implement this but because I have a common dataset for both reports so I am not able to restrict my security based on the reports and as a result both my reports Customer A is able to see data for New York as well as Illinois.

 

 Is there any way of implementing the dynamic security in Power BI where multiple reports share the same data set but can have security restrictions based on reports as well?

 

Thanks, in advance. 

 

Regards,

Siddhant

 

3 REPLIES 3
v-shex-msft
Community Support
Community Support

Hi @siddhantk989,

 

>>For example for 1 report customer A should see all data for New York but for the second report customer A should see all data only for Illinois.

Current power bi not support multiple dynamic security of same user.

 

BTW, current RLS is link with "and" logic, if you create multiple conditions, it will try to find out the records which can pass all of the conditions at same time.

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

You can link dynamic RLS with OR logic by placing the criteria in the same Role. The DAX looks like this...

 

OR(
MasterData[Ter]=LOOKUPVALUE(‘tblRLS'[Ter],’tblRLS'[Email],USERPRINCIPALNAME(),’tblRLS'[Ter],MasterData[Ter])
,
MasterData[BU]=LOOKUPVALUE(‘tblRLS'[BU],’tblRLS'[Email],USERPRINCIPALNAME(),’tblRLS'[BU],MasterData[BU])
)

 

...where MasterData is your main Data query, tblRLS is your RLS table with all of the assignments in it, and "Ter" & "BU" are the fields (which must exist in both MasterData and tblRLS) that you want to assign row-level security to based on their Email (which must exist in tblRLS).

 

I'm working with Siddhant on this issue, and our problem isn't implementing "OR" logic (that part is easy), it's that we need different types of limitations depending on which report the user is viewing, and we have lots of reports. We don't want to create dozens and dozens of Datasets (all with their own redundant data and unique RLS which must be maintained). We want to create one dataset, setup RLS roles, and then apply the appropriate role to the appropriate report.

 

Another solution would be applying report-level filters that we can "lock" so the user can't change them, but that doesn't appear to be possible either.

I should note that the OR() function only accepts two arguments, so if you want to apply more than two RLS criteria, you'll have to use the DAX OR operator, which is ||

 

MasterData[Ter]=LOOKUPVALUE(‘tblRLS'[Ter],’tblRLS'[Email],USERPRINCIPALNAME(),’tblRLS'[Ter],MasterData[Ter])
||
MasterData[BU]=LOOKUPVALUE(‘tblRLS'[BU],’tblRLS'[Email],USERPRINCIPALNAME(),’tblRLS'[BU],MasterData[BU])

||

MasterData[Cust]=LOOKUPVALUE(‘tblRLS'[Cust],’tblRLS'[Email],USERPRINCIPALNAME(),’tblRLS'[Cust],MasterData[Cust])

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors