Forum Discussion
RLS query with Multiple columns, Columns differ basis of condition.
Query1: Need to put RLS on the basis of Country,TeamName,Process in the Main Table.
Another Table called UserMapping where we have Email,Country,TeamName,Process, Role (basis of role we have to take Country&TeamName,TeamName&Process, Only Country etc.
Here I put RLS on UserMapping to know the Role,Country,TeamName,Process, So that I can filter our Main Table.
Query2: How can we store UserprincipalName() in the Table or Somewhere else so that I can use username in our Main Table/Column. (UserprincipalName() can not support in Table/Column).
Solved:
- On the basis of Email, I already done for both (Main Table and UserMapping)
- But my case is different from point 1, there is no relation.
What I did here,
- Output from UserMapping on the basis of Email (like Country,Team,Process,Combination of Country and Team,Combination of Team and Process etc.)
- Get desire output from Main Table on the basis of Point 1 (Output)
- As there are no relation between these tables, what I did, Consider UserMapping Output as a Input for Main Table.
Syntax : Manage Role sections only.
VAR _upn=USERPRINCIPALNAME()
VAR _Country='Main Table'[Country] IN CALCULATETABLE(VALUES(' UserMapping '[Country]),' UserMapping '[EMail]=_upn)
VAR _CountryAndProcess='Main Table'[CountryAndProcess] IN CALCULATETABLE(VALUES('UserMapping'[CountryAndProcess]),' UserMapping '[EMail]=_upn)
VAR _None='Main Table'[CountryAndTeam] IN CALCULATETABLE(VALUES('UserMapping'[CountryAndTeam]),' UserMapping '[EMail]="None")
return
IF(_TypeMapping="ABC" && CONTAINSSTRING([_RoleMapping],"Manager") ,_Country
,IF(_TypeMapping="XYZ" && (CONTAINSSTRING([_RoleMapping],"Country Head") || CONTAINSSTRING([_RoleMapping],"Developer")) ,_CountryAndProcess,_None))
2 Replies
Solved:
- On the basis of Email, I already done for both (Main Table and UserMapping)
- But my case is different from point 1, there is no relation.
What I did here,
- Output from UserMapping on the basis of Email (like Country,Team,Process,Combination of Country and Team,Combination of Team and Process etc.)
- Get desire output from Main Table on the basis of Point 1 (Output)
- As there are no relation between these tables, what I did, Consider UserMapping Output as a Input for Main Table.
Syntax : Manage Role sections only.
VAR _upn=USERPRINCIPALNAME()
VAR _Country='Main Table'[Country] IN CALCULATETABLE(VALUES(' UserMapping '[Country]),' UserMapping '[EMail]=_upn)
VAR _CountryAndProcess='Main Table'[CountryAndProcess] IN CALCULATETABLE(VALUES('UserMapping'[CountryAndProcess]),' UserMapping '[EMail]=_upn)
VAR _None='Main Table'[CountryAndTeam] IN CALCULATETABLE(VALUES('UserMapping'[CountryAndTeam]),' UserMapping '[EMail]="None")
return
IF(_TypeMapping="ABC" && CONTAINSSTRING([_RoleMapping],"Manager") ,_Country
,IF(_TypeMapping="XYZ" && (CONTAINSSTRING([_RoleMapping],"Country Head") || CONTAINSSTRING([_RoleMapping],"Developer")) ,_CountryAndProcess,_None))
What is the question for Query1?
For Query2: You need to decide if you want to do static RLS (based on role membership) or dynamic RLS (based on USERPRINCIPALNAME). If you data already has an email address field in a dimension table then you can add the RLS rule to that table.
