cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper I
Helper I

Multiple paths for org structure

Hello,

I recently came across RLS and decided to use it to solve my problem. It seems it will solve my issue partly. I am struggling with Path() function, because in my org structure a single employee can have 2 different managers. I'm getting message:


"Each value in 'Table 1'[Emp] must have the same value in 'Table 1'[Manager]. The value 'E2' has multiple values."

 

Table 1

ManagerEmp
M2E1
M2E2
M3E2
M3E3
M1M2
M1M3

Table 2

EmpTicket ID
E1111
E1222
E2333
E2444
E3555

 

I want M1 to see all ticketing data, M2 to see E1 and E2 Tickets, and M3 to see E2 and E3 tickets. Is there a way to build path that leads to 2 different managers?
As always any tips are appreciated.

Best Regards

3 REPLIES 3
Highlighted
Super User I
Super User I

@ksusser For your solution I am not sure PATH() will help you, becuase of the multiple managers to one employee. You could create a column in Table1 to show the "L2 Manager":

 

L2 Manager = lookupvalue('Table 1'[Manager],'Table 1'[Emp],'Table 1'[Manager])
 
Then have your RLS look for the user in Manager or L2 Manager columns.
 
[Manager]="M1" || [L2 Manager] = "M1"

 

 

Highlighted

@ksusser You could also transform it using a few merges in Transform Data window:

 

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8jVS0lFyNVSK1YGxjSBsY3S2MYRtCGT7GiGxgeKxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Manager = _t, Emp = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Manager", type text}, {"Emp", type text}}),
#"Merged Queries" = Table.NestedJoin(#"Changed Type", {"Emp"}, #"Table 2", {"Emp"}, "Table 2", JoinKind.LeftAnti),
#"Merged Queries1" = Table.NestedJoin(#"Merged Queries", {"Emp"}, #"Table 1", {"Manager"}, "Table 1", JoinKind.LeftOuter),
#"Expanded Table 1" = Table.ExpandTableColumn(#"Merged Queries1", "Table 1", {"Emp"}, {"Table 1.Emp"}),
#"Removed Columns" = Table.RemoveColumns(#"Expanded Table 1",{"Table 2", "Emp"}),
#"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Table 1.Emp", "Emp"}}),
#"Appended Query" = Table.Combine({#"Renamed Columns", #"Table 1"}),
#"Removed Duplicates" = Table.Distinct(#"Appended Query")
in
#"Removed Duplicates"

 

 

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors