Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
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
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])
User | Count |
---|---|
65 | |
27 | |
25 | |
17 | |
11 |