Forum Discussion
Power BI Embedded Row-Level Security for Multiple values of single attribute
Hi all, I have embedded a power report with one role i.e. department_id. When a user accesses the report, the report is filtered according to the department_id to which he has access. This id is passed as a USERNAME() to the role using C# application. The issue here is that how a user having access to more than one departments can view the report for multile departments. I have a role DAX as [department_id] = VALUE(USERNAME()). In case of multiple department_ids, I have to use IN clause here but it is not supported in DAX. Also, I can not do it using OR conditions because the list of departments may vary from person to person. Can anyone suggest how to filter a report based on multiple department ids (the number varying dynamically)?
14 Replies
Hello,
What I see as a solution to your problem is -
1. Create a mapping table for Roles_Departments having columns - Role_Id & Department_Id.
2. As RLS, use the DAX - [Role_Id] = USERNAME()
3. From you C# code, pass Role_Id for USERNAME & not Department_Id
This way, if you develop a custom logic to find the right Role for the logged-in user, the data could easily be filtered by all the departments associated with that role.
Hope, this helps!
Thanks,
Vinay
A workaround would be like using
VALUE(USERNAME())="XXXX"&&( [depid] ="depid1"||[depid] ="depid2")
How to manage the OR conditions as the number of deprtment ids may vary from user to user. Also, where is "depid1" and "depid2" coming from?
Hello,
There is no need to manage any OR conditions as that is why you have the Roles_Departments mapping table there for.
E.g. Your Roles_Departments mapping tables might have values like -
Role_Id | Department_Id
R1 | D1
R1 | D2
R2 | D3
R3 | D2
R3 | D3
Now, if you pass the particular Role_Id you need via USERNAME(), the related departments will get filtered which will inturn all the related tables showing data only related to those filtered departments.
Hope, this will give you more clarity. If you still have any query, please do write back.
Is it still the case that one needs to create another column to make this work?
Just to make sure I understood the question and I want to accomplish the same thing: I have a table, organizations which has a row for each organization. Organization filters is used to filter all other data tables. I want to apply a RLS filter so only data for specified organizations shows. I.e. User1 has access to Org1, Org2 and Org3. In this case I want to input "Org1|Org2|Org3" to the RLS filter.
Since I want to use DirectQuery I can't use the PATH functions. Like PATHCONTAINS(USERNAME(), [OrganizationID]) works but only for Import data.
I can't figure out how to make this work for direct query. I think I have to split the value returned by USERNAME() and then loop over that and test against each [OrganizationID] but I don't know how
Hi zq,
Would you mind posting the solutions for this problem if you have found any ?
Many Thanks.Anonymous zq RajeshAHCUSA
What you need to do is use CALCULATE to evaluate the expression for each row of the table, creating a row context for each row and filtering the data to match on a single value. I believe somthing like this would work:
='Table[DeptId]=
CALCULATE(
VALUES(Security_Table[DeptId])| ----> obtain list of values
SUMMARIZE(Security_Table|Security_Table[ADAccountName])| ----> grouped by the member
Security_Table[ADAccountName] = USERNAME() | ---> first filter matches the member to Directory
FILTER(Security_Table|Security_Table[DeptId] = EARLIER('Table[DeptId])) ------> Second Filter pulls value that matched the row
)zq did you get a solution to your post? I am looking for a similar result.
