Forum Discussion

MattyZDBA's avatar
MattyZDBA
New Member
1 year ago

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

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
 

 

 

Hierarchy
Many to Many Relationship

4 Replies

  • lbendlin's avatar
    lbendlin
    Super User
    1 year ago

    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's avatar
      MattyZDBA
      New Member
      1 year ago

      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's avatar
        lbendlin
        Super User
        1 year ago

        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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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