Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

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 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. 

 

 

Need Help
  • v-lid-msft's avatar
    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's avatar
    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 ()
            )
    )

     

     


    Best regards,

     

5 Replies

  • v-lid-msft's avatar
    v-lid-msft
    Community Support
    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,

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      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's avatar
        v-lid-msft
        Community Support
        6 years ago

        Hi Anonymous ,

         

        In our formula, we split it into multi rows, please see the [email protected]. But if you are have only one row and use "," to split it, we will try to adjust this formula.


        Best regards,