topic Re: Parent-Child Filtering In a Many-to-Many Model in DAX Commands and Tips

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

MattyZDBA — Mon, 16 Dec 2024 23:00:29 GMT

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

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

)

ALTER TABLE AbsenceDashboard.Employees

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

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)

ALTER TABLE AbsenceDashboard.EmployeeAccessGroupsMM

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

CREATE TABLE AbsenceDashboard.AccessGroupsOrgPathsMM

(

AccessGroupID int NOT NULL,

OrgPathID int NOT NULL

)

ALTER TABLE AbsenceDashboard.AccessGroupsOrgPathsMM

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

ALTER TABLE AbsenceDashboard.AccessGroupsOrgPathsMM

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

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)

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

lbendlin — Mon, 16 Dec 2024 23:23:06 GMT

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.

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

MattyZDBA — Tue, 17 Dec 2024 15:52:01 GMT

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.

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

lbendlin — Tue, 17 Dec 2024 16:20:43 GMT

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.

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

Anonymous — Thu, 19 Dec 2024 02:18:17 GMT

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