Power BI Embedded Row-Level Security for Multiple values of single attribute

[zq]

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)?

[Anonymous]

@zq did you get a solution to your post? I am looking for a similar result. 

[Anonymous]

@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
)

[Anonymous]

@Anonymous do you mean adding a new column for each, in this case, user with a 'CALCULATE' evaluation like the one you posted ? 

[Anonymous]

Hi @Anonymous ,

You don't need a column to do this. You can use the code in the DAX Filter under the role manager and assign it to the table you're wanting to filter.

Thanks

[RajeshAHCUSA]

Hi zq, 
Would you mind posting the solutions for this problem if you have found any ?

Many Thanks.

[Anonymous]

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

[vinaypugalia]

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

[Eric_Zhang]

@zq

A workaround would be like using

VALUE(USERNAME())="XXXX"&&(
[depid] ="depid1"||[depid] ="depid2")

[zq]

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?

[vinaypugalia]

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.

[Anonymous]

I have one table where I have to check two roles. One is "TenantID" and User Level. Can you please help with this.

TenantID = username() && User Level = username() 

Above this, I set the roles?

Report should be filtered via Tenant and then user level role.  

Please let me know how to call this role in embedded code 

var generateTokenRequestParameters = new GenerateTokenRequest("View", null, identities: new List<EffectiveIdentity> { new EffectiveIdentity(username: "username", roles: new List<string> { "roleA", "roleB" }, datasets: new List<string> { "datasetId" }) });

var tokenResponse = await client.Reports.GenerateTokenInGroupAsync("groupId", "reportId", generateTokenRequestParameters);

or 

{ "accessLevel": "View", "identities": [ { "username": "EffectiveIdentity", "roles": [ "Role1", "Role2" ], "datasets": [ "fe0a1aeb-f6a4-4b27-a2d3-b5df3bb28bdc" ] } ] }

[vinaypugalia]

Hello Mayank,

You can probably, create a computed column which is a combination of TenantId & User Level. Once this is done, you can then pass appropriate value in the UserName(), which can then be used to apply the rule on the newly created computed column.

Hope, this helps!

[zq]

Thanks vinaypugalia, i got your point. My previous reply was based on the comment by v-lvzhan-msft. However, I don't have roles in my scenario. Also, such table needs to be modified everytime when the user to depratment mapping changes?

[vinaypugalia]

Hello,

I do understand that you currently do not have ROLES in your application, but you need to bring in this concept to get rid of multiple OR conditions. Also, if there is any change in User-Department mappings, any solution to come up with will need a change and having a mapping table for it will be the most scalable of them.