topic Re: Power BI Embedded Row-Level Security for Multiple values of single attribute in Developer

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

zq — Fri, 17 Feb 2017 12:44:54 GMT

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

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

Eric_Zhang — Mon, 20 Feb 2017 14:47:26 GMT

@zq

A workaround would be like using

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

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

vinaypugalia — Tue, 21 Feb 2017 11:06:24 GMT

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

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

zq — Fri, 24 Feb 2017 05:14:44 GMT

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?

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

vinaypugalia — Fri, 24 Feb 2017 07:46:40 GMT

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.

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

zq — Fri, 24 Feb 2017 07:56:41 GMT

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?

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

vinaypugalia — Fri, 24 Feb 2017 09:25:26 GMT

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.

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

Anonymous — Mon, 18 Jun 2018 06:30:07 GMT

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

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

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

vinaypugalia — Mon, 18 Jun 2018 06:43:06 GMT

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!

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

Anonymous — Tue, 04 Jun 2019 12:32:44 GMT

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

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

RajeshAHCUSA — Thu, 05 Dec 2019 22:46:37 GMT

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

Many Thanks.

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

Anonymous — Thu, 13 Feb 2020 17:26:00 GMT

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

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

Anonymous — Tue, 18 Feb 2020 13:35:18 GMT

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

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

Anonymous — Wed, 01 Jul 2020 19:45:14 GMT

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

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

Anonymous — Fri, 23 Apr 2021 18:47:13 GMT

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