Row level security, multiple roles

Hi All, I am looking to implement some security to my powerbi report and the intention is that the users should only see the relevent cost centers (as below). I have tried looking online but the...

Need Help

v-lid-msft

6 years ago

Hi Anonymous ,

If your have a user table visual such as following.

Email	L0	L1	L2
[email protected]	Total Company
[email protected]		Sweden
[email protected]		Denmark
[email protected]			Copenholm
[email protected]	Total Company
[email protected]		Norway
[email protected]			Stockholm
[email protected]			Oslo
[email protected]			Copenhagen
[email protected]			Stockholm

And you have two columns in cost center table, such as CostL1 and CostL2, Then we can try to create RLS roles in Cost Center Table:

var t = Filter('User',[Email]=USERPRINCIPALNAME ())
return if(Countrows(Filter(t,[L0]="Total Company"))>0,TRUE(),[CostL1] in SelectColumns(t,"Value",[L1]) || [CostL2] in SelectColumns(t,"Value",[L2]))

Best regards,

v-lid-msft

6 years ago

Hi Anonymous ,

Sorry for late reply, we can use the following rules:

VAR t =
    FILTER ( 'UserTable', [Email] = USERPRINCIPALNAME ( ) )
RETURN
    IF (
        COUNTROWS ( FILTER ( t, [L0] = "Total Company" ) ) > 0,
        TRUE (),
        IF (
            SUMX ( t, IF ( CONTAINSSTRING ( [L1], [CostL1] ), 1, BLANK () ) ),
            TRUE (),
            FALSE ()
        )
            || IF (
                SUMX ( t, IF ( CONTAINSSTRING ( [L2], [CostL2] ), 1, BLANK () ) ),
                TRUE (),
                FALSE ()
            )
    )