Forum Discussion

JohnLow's avatar
JohnLow
Helper I
5 years ago
Solved

Row Level Security Question

Hi,

 

I'm new to RLS and need a little help. I've 3 tables Users, Network and Sales. Users has a relationship to Network which in turn has a relationship to Sales. Users have 3 roles: Network User, Admin and Finance. Admin and Finance should be able to see everything in the Sales table whereas the network user should only see what they have been assigned via the network table. I'm a little unsure as to whether I need to create a seperate role for all 3 roles. I've created a network role which seems to be function ok but would like that the finance and admin role override the network role and am unsure how to do that. 

I've included the the pbix file here. 

 

Thanks for the support. 

 

 

Need Help
  • sayaliredij's avatar
    sayaliredij
    5 years ago

    Hi JohnLow 

     

    Could you try applying a security filter

     

    I tried at my end and it works

     

    For Finance user - everything is visible and for network user - only data related to him

     

     

15 Replies

  • sayaliredij's avatar
    sayaliredij
    Solution Sage
    5 years ago

    JohnLow 

     

    Could you check this file 

     

    sorry I missed that the connection between Network and sales was one way

     

    I enabled the following setting and then it worked

     

     

    • JohnLow's avatar
      JohnLow
      Helper I
      5 years ago

      sayaliredij , thanks again. I just realised that the relationship between network and sales is many to many. I should have been clearer in my model. I've attached the file again. Sorry for the confusion. 

       

      File 

  • sayaliredij's avatar
    sayaliredij
    Solution Sage
    5 years ago

    Hi JohnLow 

     

    Could you try applying a security filter

     

    I tried at my end and it works

     

    For Finance user - everything is visible and for network user - only data related to him

     

     

  • sayaliredij's avatar
    sayaliredij
    Solution Sage
    5 years ago

    You can create 2 roles 

    1. Admin Role - where you can put all the people who would have access to all numbers 

    2. Network role with DAX Formule = [Username] = USERNAME()

     

    In power bi service while defining RLS configuration for Admin Role - you can put all the people from administrator and finance and for other network role - people from network departments (all other people)

  • sayaliredij's avatar
    sayaliredij
    Solution Sage
    5 years ago

    In case you don't want to use 2 roles

     

    you can use the following DAX for the user table 

     

    If(MaxX(
    Filter(
    Users,
    Users[Username] = Username())
    ,Users[Role])= "Network user",
    Users[Username] = Username(),
    1=1
    )

    • JohnLow's avatar
      JohnLow
      Helper I
      5 years ago

      sayaliredij Thanks. I have a similar DAX statement for my network role in the pbix file above. This works ok. I'm just wondering do I need another role. Could you have a look at the pbix file attached please?

      [NetworkID] IN
SELECTCOLUMNS (
    FILTER (
        Network,
        Network[UserId]
            = LOOKUPVALUE ( Users[UserID], Users[UserName], USERPRINCIPALNAME() )
    ),
    "NetworkID", [NetworkID]
)

       

      • JohnLow's avatar
        JohnLow
        Helper I
        5 years ago

        Ideally what I would like is one role. I would create a new column in the Users table with an override flag. 

        OverRideFlag = IF(
	ISERROR(
		SEARCH("Network", Users[Role])
	),
	"Y",
	"N"
)

         The DAX would then say, if User is a network user(override flag = no) follow the DAX in message 4  above, if not (override flag = yes) access everything in the Sales table. I could then add the everyone group to the powerbi online service and then it would filter automatically? The only manual work I would have to do is to share the report. 

        Would be great if you could help me with the DAX, thanks again.

  • JohnLow's avatar
    JohnLow
    Helper I
    5 years ago

    Hi, could anyone help me a little further here? The RLS works fine for Network users, but all other users don't see anything and they should see everything. Here's the file: FILE 

     

    Thanks again. 

  • sayaliredij's avatar
    sayaliredij
    Solution Sage
    5 years ago

    JohnLow  It seems DAX provided by you is not handling all the scenarios. I think we don't need to add 2 DAX in sales and user

     

    I added only one DAX - 

     

    and when I view it as 

     

    I see all the data 

     

    also check the file 

    • JohnLow's avatar
      JohnLow
      Helper I
      5 years ago

      sayaliredij Thank you for the support but this doesn't handle the scenarios for the network users. For example, Justin below (UserID 4) should only see the first 2 rows of the table below, he shouldn't see the row with network name, Sparkling Socials.