Create Slicer & Manage Roles in Dynamic Security Roles

[ADSL]

Hi BI Community Team,

I have a challenging to create the slicer and setup the rule in dynamic security role that I have 02 tables: Sales Rep & Sales Order.

For SM & RSM, they can see everything. SUP can see only their team member. 

For Slicer, we want to create 02: Team Code | SUP | Sales Rep. 

Sales Rep Table

Any suggestion to create --- Slicer & Manage Role? 

[v-jianboli-msft · Accepted solution]

Hi @ADSL ,

Please try:

First creaete a new table in power query, here is the M code:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("nZVNb9swDIb/ipFzMZh0Pm/LChTFgACDs8sW9MCkSuzMVgLZLZb9+km2KZfdBky6xDIjPHjJl6R3u0m+3aQwuZs8UlmpW/KJDJ2psYFi3x8/0n7/4XCpbShXp/KiqUq2VKkm2ZCmkzL2j+9pZn/1S1VNnu52kwF5T6ZS7UUnX0ypmtZGDtfu9Ab5d1IvakChQxVUk7b3lJN2qO0zALIeBG3IvFKSX/Z7+1Ib+3wDWRtF/0hMgJycb6pplC4tS5WHwgZuxh0iaC7wmX5VN62Tr4pOL8oGzm13isBNbeCBSnOkn8kj6eqibeBYdKf/xqHHzdzdSp1JPxtKNsejap2NVHenCODc3a3pmYqb1df8KLXzk4r+GAjc5mmnsL+Wq2vi3hqjrrM/msP+a5T1rKW2fFWetR4bzcJQwHCAYRQsk7CMaVkcDuYC1706HMzjcFIdsDqIVCdtAPYBIo1YCtxyoC1DYL5FYCq1TVnbNA4HEgeMgygcylSRc8W4ZPsBGbuOmy5Om6wccuUwrnLpQuAWA20RAss8bCVgqwG2ioKhHC7k4cKg4RpxqcSljEvjUpU2sAtBJozaZPsity8Gte+oTVaOCxdUt6nXJj1FNhWDXB1xcikhLyUMWkoel8nKZVy5LKhyHgcyWeBkITJZOVzI04VB4+Vx6bstx+JCYDNfOTkPGc9DFjQPHgdyxwEvOQjach6H7776/rMf9Ckc1cmFDrzQIWihzz1O+grsKwT56nEoa4dcOwyq3ahOOgvsLAQ5a3FPvwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"User Code" = _t, #"User Name" = _t, Email = _t, Position = _t, #"Team Code" = _t, #"Manager Code" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"User Code", type text}, {"User Name", type text}, {"Email", type text}, {"Position", type text}, {"Team Code", type text}, {"Manager Code", type text}}),
    #"Removed Other Columns" = Table.SelectColumns(#"Changed Type",{"Team Code"}),
    #"Removed Duplicates" = Table.Distinct(#"Removed Other Columns"),
    #"Added Custom" = Table.AddColumn(#"Removed Duplicates", "Custom", each Table.SelectColumns(
Table.SelectRows(SalesRep,each Text.Contains([User Code],"RSM")),"User Code")),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"User Code"}, {"Custom.User Code"}),
    #"Renamed Columns" = Table.RenameColumns(#"Expanded Custom",{{"Custom.User Code", "RSM"}}),
    #"Added Custom1" = Table.AddColumn(#"Renamed Columns", "SM", each let 
RSM = _[RSM]
in 
Table.SelectColumns(
Table.SelectRows(SalesRep,each [Manager Code] = RSM ),"User Code")),
    #"Expanded SM" = Table.ExpandTableColumn(#"Added Custom1", "SM", {"User Code"}, {"SM.User Code"}),
    #"Renamed Columns1" = Table.RenameColumns(#"Expanded SM",{{"SM.User Code", "SM"}}),
    #"Added Custom2" = Table.AddColumn(#"Renamed Columns1", "Custom", each let 
SM = _[SM]
in 
Table.SelectColumns(
Table.SelectRows(SalesRep,each [Manager Code] = SM ),"User Code")),
    #"Expanded Custom1" = Table.ExpandTableColumn(#"Added Custom2", "Custom", {"User Code"}, {"Custom.User Code"}),
    #"Renamed Columns2" = Table.RenameColumns(#"Expanded Custom1",{{"Custom.User Code", "ASM"}}),
    #"Added Custom3" = Table.AddColumn(#"Renamed Columns2", "Custom", each let 
ASM = _[ASM]
in 
Table.SelectColumns(
Table.SelectRows(SalesRep,each [Manager Code] = ASM ),"User Code")),
    #"Expanded Custom2" = Table.ExpandTableColumn(#"Added Custom3", "Custom", {"User Code"}, {"Custom.User Code"}),
    #"Renamed Columns3" = Table.RenameColumns(#"Expanded Custom2",{{"Custom.User Code", "Sales Rep"}})
in
    #"Renamed Columns3"

Output:

RLS:

SWITCH (
    TRUE (),
    LOOKUPVALUE ( 'Sales Rep'[User Code], 'Sales Rep'[Email], USERNAME () )
        IN ALL ( 'For Slicer'[Sales Rep] ), [Email] == USERNAME (),
    LOOKUPVALUE ( 'Sales Rep'[User Code], 'Sales Rep'[Email], USERNAME () )
        IN ALL ( 'For Slicer'[ASM] ),
        [User Code]
            IN UNION (
                SELECTCOLUMNS (
                    FILTER (
                        'For Slicer',
                        [ASM] = LOOKUPVALUE ( 'Sales Rep'[User Code], 'Sales Rep'[Email], USERNAME () )
                    ),
                    "SR", [Sales Rep]
                ),
                { LOOKUPVALUE ( 'Sales Rep'[User Code], 'Sales Rep'[Email], USERNAME () ) }
            ),
    LOOKUPVALUE ( 'Sales Rep'[User Code], 'Sales Rep'[Email], USERNAME () )
        IN UNION ( ALL ( 'For Slicer'[SM] ), ALL ( 'For Slicer'[RSM] ) ), [Email] == [Email]
)

Final output:

Best Regards,

Jianbo Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.