Multiple paths for org structure

[Anonymous]

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

Manager	Emp
M2	E1
M2	E2
M3	E2
M3	E3
M1	M2
M1	M3

Table 2

Emp	Ticket ID
E1	111
E1	222
E2	333
E2	444
E3	555

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

[DataZoe]

@Anonymous 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"

[DataZoe]

@Anonymous 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"

[amitchandak]

@Anonymous , see if these can help

RLS - Row Level security

https://www.blue-granite.com/blog/using-dynamic-row-level-security-with-organizational-hierarchies

https://radacad.com/dynamic-row-level-security-with-organizational-hierarchy-power-bi

https://blog.tallan.com/2018/04/10/row-level-security-in-power-bi-part-1-roles-and-users/

https://community.powerbi.com/t5/MBAS-Gallery/Microsoft-Power-BI-Unleash-row-level-security-patterns-in-Power/td-p/712613
https://docs.microsoft.com/en-us/power-bi/service-admin-rls
https://radacad.com/what-do-you-need-to-implement-dynamic-row-level-security-in-power-bi