Embedded RLS

[dinesh123]

@tamerj1 
Hi all,

We are using Embedding Power BI. In that we are using RLS. In general secenarios, RLS works based on Username(), Userprincipalname() where it will return either user name or user email id. With embedded power BI RLS scenario, we can get any value in the username() as part of the requirement on which value we should filter the data. As part of RLS, i am getting a string with list of values where user can see the report only with list of values corresponding data. Here string contains "Program1,Program2,Program3.." like so on. I should able to read this string and filter the data for those program related data. As i get the list of values in the form of string i need to store this as a list or column and then only can filter.

For Example:

{

  "accessLevel": "View",

  "datasetId": "cfafbeb1-*****-a46fb27ff229",

  "identities": [

    {

      "username": "Program1",

      "roles": [

        "sales"

      ],

      "datasets": [

        "cfafbeb1-******-a46fb27ff229"

      ]

    }

  ]

}

Here we are passing one Program from Java SDK. We were able to apply RLS for one value. However,

{

  "accessLevel": "Create",

  "datasetId": "cfafbeb1-8******e-a46fb27ff229",

  "identities": [

    {

      "username": "Program1,Program2,Program3,….",

      "roles": [

        "sales"

      ],

      "datasets": [

        "cfafbeb1-80*****e-a46fb27ff229"

      ]

    }

  ]

}

But we want to pass more programs from username but we are getting output as single string not as individual Programs.

Here we need to pass multiple values from Username and read it as individual Programs as values in Embedded PowerBI not as single string.

For reference: https://learn.microsoft.com/en-us/power-bi/developer/embedded/cloud-rls

[tamerj1 · Accepted solution]

Hi @dinesh123 
Please try

[Program] IN
VAR String = USERNAME ( )
VAR Items =
    SUBSTITUTE ( String, ",", "|" )
VAR Length =
    COALESCE ( PATHLENGTH ( Items ), 1 )
VAR T1 =
    GENERATESERIES ( 1, Length, 1 )
RETURN
    SELECTCOLUMNS ( T1, "Project", PATHITEM ( Items, [Value] ) )

[tamerj1 · Accepted solution]

Hi @dinesh123 
Please try

[Program] IN
VAR String = USERNAME ( )
VAR Items =
    SUBSTITUTE ( String, ",", "|" )
VAR Length =
    COALESCE ( PATHLENGTH ( Items ), 1 )
VAR T1 =
    GENERATESERIES ( 1, Length, 1 )
RETURN
    SELECTCOLUMNS ( T1, "Project", PATHITEM ( Items, [Value] ) )

[dinesh123]

For Example:

{

  "accessLevel": "View",

  "datasetId": "cfafbeb1-*****-a46fb27ff229",

  "identities": [

    {

      "username": "Program code = P1,P2,P3,….":“Child code= C1,C2,C3”

      "roles": [

        "sales"

      ],

      "datasets": [

        "cfafbeb1-******-a46fb27ff229"

      ]

    }

  ]

}

Here we are passing Program code and Child code from Java SDK, now with your help we are able to pass the multiple program codes, However, can we pass Child code also corresponding to that Program code?
As we are using Program code and child code as two different slicers

Ex:

Program code	Child Code
P1	C1
P1	C2,C3
P1	C4
P2	C5,C6
P2	C7

[tamerj1]

@dinesh123 

If you place USERNAME ( ) in a card visual, what do you see?

[dinesh123]

P1,P2,P3,... If I place username() in card. I passed only for program code in SDK.  Not tested for childcode

[dinesh123]

Sorry if we pass Username= "p1,p2,p3:c1,c2,c3.... (In Java SDK) Can we read it in embedded Rls as two fields?

[dinesh123]

For one field (program code) your code work fine. Now I need to read program and child, here we are using two slicers one for program and other for child.

[tamerj1]

@dinesh123 

I thought you've already done that. I've never tried 😅

[dinesh123]

Hi @tamerj1, @daXtreme, @JihwanKim 
I'm trying to pass Username as "P1,P2,P3,..:C1,C2,C3,..." in embedded RLS(by Java SDK) from two different slicers. Were P and C are two different fields.

Whenever I try with above sicenario i'm able to pass only P values, but I need to pass P and C values individually.
I'm giving this code in Manage roles in RLS in Powerbi. Where P is for Program code and C is for Childcode.

[parent_org_cd] IN {
Var input1 = Username()
VAR indexOfSeparator = FIND ( ":", input1, 1, BLANK () )
VAR String = LEFT ( input1, LEN ( input1 ) - indexOfSeparator )
VAR Items =
SUBSTITUTE ( String, ",", "|" )
VAR Length =
COALESCE ( PATHLENGTH ( Items ), 1 )
VAR T1 =
GENERATESERIES ( 1, Length, 1 )
RETURN
SELECTCOLUMNS ( T1, "Project", PATHITEM ( Items, [Value] ) )}
&& [child_org_cd] IN{
Var input2 = Username()
VAR indexOfSeparator1 = FIND ( ":", input2, 1, BLANK () )
VAR String1 = Right ( input2, LEN ( input2 ) - indexOfSeparator1 )
VAR Items1 =
SUBSTITUTE ( String1, ",", "|" )
VAR Length1 =
COALESCE ( PATHLENGTH ( Items1 ), 1 )
VAR T2 =
GENERATESERIES ( 1, Length1, 1 )
RETURN
SELECTCOLUMNS ( T2, "Project1", PATHITEM ( Items1, [Value] ) )}

[tamerj1]

@dinesh123 

"Were P and C are two different fields."
What are these fields?

[dinesh123]

P is program code, we will get n number of program codes from SDK for example P1,P2,.... Similarly C is for child code C1, C2,..... Is the sequence.

We already have data of program code and child code in our PBI report.

From Java SDK we will get few program codes and corresponding child codes which are passed as username = "P1,P2:C1,C2" we are trying to create a RLS role in such a way that the user can see only data related to P1,P2,C1,C2 which are passed by Java SDK not all data. Here program code(P1,P2,..) is one column and child code (C1,C2,...) Is another column of same table.

[tamerj1]

@dinesh123 

Your approach is correct. The only thing is that you you need to define the role separately for each field. In other words, one role for the program field and one role for the child code field. 

[dinesh123]

Can we pass two roles through java SDK?

[tamerj1]

@dinesh123 
Are both fields in the same table? I can think of creating a new column that combines the program code with child code like P,C then you can pass the allowable combinations for each username. This way the RLS will be applied to one field. Theoretically, it should work.

[dinesh123]

Yes both are in same table.

But unable to apply rls on both p and c. It's applying on only program code or if I give program and child in rls(as shown in the code above) then it's not at all reading the both values program and child. Showing empty in slicers..

[tamerj1]

@dinesh123 

My suggestion was to create a new column in your table that combines the program code with the child code seperated by comma. This column should contain all the possible combinations (of course will not contain unique values). On the other hand you can pass the shape of combination (P,C) using Java SDK and then apply the dax on the newly created field. 

other possible method is to create two dimension tables (one for each field) create one to many relationships between dimensions and the your table then apply a role on rach of the dimension tables. 

However, it is worth it that you try your method but with || operator instead of the && operator. 

let me know if we can connect via zoom or teams?

[dinesh123]

Hi @tamerj1 ,
Thank you for your response, may I know your available Time zone to connect, so that I can plan accordingly.

[tamerj1]

@dinesh123 
Dubai

Can we connect now?

[dinesh123]

Can you please provide your mail ID?

[tamerj1]

tamer_juma@yahoo.com

[dinesh123]

Hi @tamerj1 Mailed you zoom meeting link, can you please join.