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]

@dinesh123 

Please check your email 

[dinesh123]

Hi @tamerj1 Sorry for inconvinience,  Resent the link please connect.

[dinesh123]

I have resent zoom link.

[dinesh123]

Hi @tamerj1 ,
Thank you for your help over the call.
When I try pass the Same code with Java SDK its not working. The code i'm passing is "P1,P2:" Where P1 and P2 are program codes and after collon no child code so kept empty. 

Here is the code for Child code Managerole RLS. 

[Child_code] IN

Var Input = Username()
Var IndexSep = FIND(":", Input, 1, BLANK())

VAR String = RIGHT(Input,LEN(Input)-IndexSep)

Var String2 = COALESCE(String, Concatenatex(values(ChildOrg[Child_code]),ChildOrg[Child_code],","))

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

[tamerj1 · Accepted solution]

@dinesh123 
Please try

=
[Child_code]
    IN
    VAR Input =
        USERNAME ()
    VAR Length =
        LEN ( Input )
    VAR IndexSep =
        COALESCE ( FIND ( ":", Input, 1, BLANK () ), Length )
    VAR String =
        IFERROR ( RIGHT ( Input, Length - IndexSep ), BLANK () )
    VAR String2 =
        COALESCE (
            String,
            CONCATENATEX ( VALUES ( ChildOrg[Child_code] ), ChildOrg[Child_code], "," )
        )
    VAR Items =
        SUBSTITUTE ( String2, ",", "|" )
    VAR Length =
        COALESCE ( PATHLENGTH ( Items ), 1 )
    VAR T1 =
        GENERATESERIES ( 1, Length, 1 )
    RETURN
        SELECTCOLUMNS ( T1, "Project", PATHITEM ( Items, [Value] ) )

[dinesh123]

Hi @tamerj1 
Can you please check email, I have mailed you.

[tamerj1]

Hi @dinesh123 
Please use

=
[ChildCode_RLS]
    IN
    VAR Input =
        USERNAME ()
    VAR Items1 =
        SUBSTITUTE ( Input, ":", "|" )
    VAR String1 =
        PATHITEM ( Items1, 2 )
    VAR String2 =
        IF (
            String1 = "",
            CONCATENATEX (
                VALUES ( ChildCode[ChildCode_RLS] ),
                ChildCode[ChildCode_RLS],
                ","
            ),
            String1
        )
    VAR Items2 =
        SUBSTITUTE ( String2, ",", "|" )
    VAR Length =
        COALESCE ( PATHLENGTH ( Items2 ), 1 )
    VAR T1 =
        GENERATESERIES ( 1, Length, 1 )
    RETURN
        SELECTCOLUMNS ( T1, "@ChildCode", PATHITEM ( Items2, [Value] ) )

[dinesh123]

Hi @tamerj1 Thank you tamer for your help.
Sorry to disturb again. I have mailed can you please check.


This code works fine.

=
[Child_code]
    IN
    VAR Input =
        USERNAME ()
    VAR Length =
        LEN ( Input )
    VAR IndexSep =
        COALESCE ( FIND ( ":", Input, 1, BLANK () ), Length )
    VAR String =
        IFERROR ( RIGHT ( Input, Length - IndexSep ), BLANK () )
    VAR String2 =
        COALESCE (
            String,
            CONCATENATEX ( VALUES ( ChildOrg[Child_code] ), ChildOrg[Child_code], "," )
        )
    VAR Items =
        SUBSTITUTE ( String2, ",", "|" )
    VAR Length =
        COALESCE ( PATHLENGTH ( Items ), 1 )
    VAR T1 =
        GENERATESERIES ( 1, Length, 1 )
    RETURN
        SELECTCOLUMNS ( T1, "Project", PATHITEM ( Items, [Value] ) )

[dinesh123]

Hi @tamerj1 Kudos!!!!!!
Code is working fine exactly as we expected to be. Thank you very much once again for your help and support.

[dinesh123]

Hi @tamerj1 Kudos 
Code is working fine exactly how we expected to be. Thank you so much once again for your time and support. 

[dinesh123]

@tamerj1 
Hi Tamer,
Thanks for your immediate respose on weekend also. It is working fine. Thanks alot!!!!