Row level security, multiple roles

[Anonymous]

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 best that I could find are those with the organisational hierarchy. The issue here is that I do not have an organanisation hierarchy but instead Cost center hierarchy. Maybe I am mistaken here. But if someone could point me in the right direction that would be so great. 

In addition to this, I also have the issue where for e.g. Anders has multiple cost centers to take care of such as Copenhagen and Stockholm ( See below). How would I address this issue? 

Thanks all in advance. 

[v-lid-msft · Accepted solution]

Hi @Anonymous ,

If your have a user table visual such as following.

Email	L0	L1	L2
A@abc.com	Total Company
B@abc.com		Sweden
C@abc.com		Denmark
D@abc.com			Copenholm
E@abc.com	Total Company
F@abc.com		Norway
G@abc.com			Stockholm
H@abc.com			Oslo
I@abc.com			Copenhagen
I@abc.com			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 · Accepted solution]

Hi @Anonymous ,

If your have a user table visual such as following.

Email	L0	L1	L2
A@abc.com	Total Company
B@abc.com		Sweden
C@abc.com		Denmark
D@abc.com			Copenholm
E@abc.com	Total Company
F@abc.com		Norway
G@abc.com			Stockholm
H@abc.com			Oslo
I@abc.com			Copenhagen
I@abc.com			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,

[Anonymous]

Thanks for much for the solution!!! It greatly helped me! 🙂

I just have one more question, if the user has access to multiple cost centers like with the case of anders@abc, where he has access to both copenhagen and stockholm, how would i change the formula?

[v-lid-msft]

Hi @Anonymous ,

In our formula, we split it into multi rows, please see the I@abc.com. But if you are have only one row and use "," to split it, we will try to adjust this formula.

Best regards,

[Anonymous]

How would you adjust the formula?

Thanks so much for your help again 🙂

[v-lid-msft · Accepted solution]

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

Best regards,