Parent-Child Filtering In a Many-to-Many Model

[MattyZDBA]

I'm working on a dashboard reporting Absences organization wide. Below is a baseline data model based on our timekeeping software's data tier.

In essence, we have a hierarchical org chart (OrgPath), with Employees mapped to one node at any time. Some employees are managers and are assigned AccessGroup containers which are mapped to 1+ OrgPaths.

The intention of the dashboard is for RLS to filter the dashboard based on USERNAME() or another function to only display personnel in OrgPaths that are directly part of the manager's access group, AND for any child OrgPaths for those explicit parent OrgPaths.

Building the PATH for the OrgPath is straightforward in DAX, but in RLS, I've tried using SELECTEDVALUE in this fashion but no luck

  OrgPaths[OrgPathID] IN PATH(SELECTEDVALUE(AccessGroupsOrgPathsMM(OrgPathID), OrgPaths[ParentOrgPathID])

What should be the DAX expression to filter the Absences table on Employees belonging to the 1+ OrgPaths part of the manager's selected AccessGroup?

ERD below

T-SQL DDL for the model is here.

CREATE SCHEMA AbsenceDashboard

GO

CREATE TABLE AbsenceDashboard.OrgPaths

(

OrgPathID int IDENTITY(1,1) NOT NULL PRIMARY KEY,

OrgPath varchar(50),

ParentOrgPathId int

)

CREATE TABLE AbsenceDashboard.Employees

(

EmployeeID int IDENTITY(1,1) NOT NULL PRIMARY KEY,

EmployeeName varchar(50),

OrgPathID int NOT NULL,

ManagerID int

)

GO

ALTER TABLE AbsenceDashboard.Employees 

ADD CONSTRAINT FK_1 FOREIGN KEY(OrgPathID) REFERENCES AbsenceDashboard.OrgPaths(OrgPathID)

GO

CREATE TABLE AbsenceDashboard.AccessGroups

(

AccessGroupID int IDENTITY(1,1) NOT NULL PRIMARY KEY,

AccessGroup varchar(50)

)

CREATE TABLE AbsenceDashboard.EmployeeAccessGroupsMM

(

EmployeeID int NOT NULL,

AccessGroupID int NOT NULL

)

ALTER TABLE AbsenceDashboard.EmployeeAccessGroupsMM

ADD CONSTRAINT FK_2 FOREIGN KEY(EmployeeID) REFERENCES AbsenceDashboard.Employees(EmployeeID)

GO

ALTER TABLE AbsenceDashboard.EmployeeAccessGroupsMM

ADD CONSTRAINT FK_3 FOREIGN KEY(AccessGroupID) REFERENCES AbsenceDashboard.AccessGroups(AccessGroupID)

GO

CREATE TABLE AbsenceDashboard.AccessGroupsOrgPathsMM

(

AccessGroupID int NOT NULL,

OrgPathID int NOT NULL

)

GO

ALTER TABLE AbsenceDashboard.AccessGroupsOrgPathsMM

ADD CONSTRAINT FK_4 FOREIGN KEY(OrgPathID) REFERENCES AbsenceDashboard.AbsenceDashboard(OrgPathID)

GO

ALTER TABLE AbsenceDashboard.AccessGroupsOrgPathsMM

ADD CONSTRAINT FK_5 FOREIGN KEY(AccessGroupID) REFERENCES AbsenceDashboard.AccessGroups(AccessGroupID)

GO

CREATE TABLE AbsenceDashboard.Absences

(

AbsenceID int NOT NULL PRIMARY KEY,

EmployeeID int,

DateAbsent date,

DateReported datetime DEFAULT GETDATE()

)

ALTER TABLE AbsenceDashboard.Absences

ADD CONSTRAINT FK_6 FOREIGN KEY(EmployeeID) REFERENCES AbsenceDashboard.Employees(EmployeeID)

GO

[Anonymous]

Hi @MattyZDBA ,

Whether the advice given by lbendlin has solved your confusion, if the problem has been solved you can mark the reply for the standard answer to help the other members find it more quickly. If not, please point it out.

Best Regards,

Neeko Tang

[lbendlin]

1. I don't see a M:M  in your data model

2. there is no "parent"  or "child"  in a M:M - both sides are equal rights citizens.

[MattyZDBA]

Updated ERD here. The core M:M relationship should be in AccessGroupOrgPaths. A given Access Group can have one ore more associated OrgPaths, while a given OrgPath can be associated with 1+ Access Groups. The typical scenario would be a VP in the org having an access group which is a superset of the orgpaths for a Director that reports to the VP. In practice, an employee should only be assigned one access group but i modeled for more than one in case of ad-hoc or temporary requirements.

[lbendlin]

For these scenarios you would use dynamic RLS rather than static RLS. You would have an externally maintained reference table that lists all access mappings for each USERPRINCIPALNAME  (email address, basically).  This gives you full flexibility to map the corner cases.