Forum Discussion

JohnLow's avatar
JohnLow
Icon for Helper I rankHelper 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,...
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

     

     

sayaliredij's avatar
sayaliredij
Icon for Solution Sage rankSolution 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
    Icon for Helper I rankHelper 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
      Icon for Helper I rankHelper 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.

    • sayaliredij's avatar
      sayaliredij
      Icon for Solution Sage rankSolution Sage
      5 years ago

      I think one role with DAX above should be sufficient. I checked pbix file attached and tried with only one role and viewing it as different username as mentioned in the Users Table  and I was getting expected result 

      • JohnLow's avatar
        JohnLow
        Icon for Helper I rankHelper I
        5 years ago

        sayaliredij, I'm not sure. If I add for example, Leah, a non network user to the network group online, she doesn't see everything. She should see everything. 

        If I test with Leah, a non network user, she doesn't see anything in the table: