Forum Discussion

dinesh123's avatar
dinesh123
Icon for Helper II rankHelper II
3 years ago
Solved

Embedded RLS

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 e...
  • tamerj1's avatar
    tamerj1
    3 years ago

    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's avatar
    tamerj1
    3 years ago

    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's avatar
dinesh123
Icon for Helper II rankHelper II
3 years ago

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's avatar
tamerj1
Icon for Community Champion rankCommunity Champion
3 years ago

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's avatar
    dinesh123
    Icon for Helper II rankHelper II
    3 years ago

    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's avatar
    dinesh123
    Icon for Helper II rankHelper II
    3 years ago

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

  • dinesh123's avatar
    dinesh123
    Icon for Helper II rankHelper II
    3 years ago

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

  • tamerj1's avatar
    tamerj1
    Icon for Community Champion rankCommunity Champion
    3 years ago

    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's avatar
    dinesh123
    Icon for Helper II rankHelper II
    3 years ago

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