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) GODate hierarchy
Hi, I have a general question about Dax in Excel. I have a fact table and in Power Queryand from this fact table I make a date table. I do this because I want to use the date table as a slicer. Then I push both table from Power Query to the Data model so that I can work with DAX. When I make a pivot table in Excel I am not seeing the date hierarchy (Year, Month, Day) that I would expect from the date table. However, this hierarchy is being created when I use the date column in my fact table. My question is, why is the hierarchy not being created when using the date table? Thanks, Naveen
Parent Child Hierarchy - Sum all Leafs on each NodeLevel
Hello everyone I hope you can help me with my Parent Child Hierarchy problem. I want to use a Dax measure to make the amount from the fact table (on hierarchy leaves) available on all node levels. The specialty is that I want to use the flat hierarchy column “Name” from the table “Hierarchy” in the visual. Without the following solutions: Using a hierarchy Lev1-3 or LevName1-3 in scope of the visual Calculation as a column and not as a separate dax measure In the attached picture you can see the test data and the desired result.
Applying different weights on each level of hierarchy
Hello, I have PnL fact table like this: fact_table([id],[date],[account_key],[sales_amount]) and dimension for PnL hierarchy like this: dimension_table([account_key],[level_1_name],[level_2_name],[level_2_weight],[level_3_name],[level_3_weight],[level_4_name],[level_4_weight],[level_5_name],[level_5_weight]) relationship between those table is M:N through account_key. I need to calculate sum of sales_amount with applying the correct weight when going from a lower level of the hierarchy to a higher one. Not all levels for each account is valid -> each level except one can be null. I tried everything with INSCOPE, RELATED, SWITCH and SUMX but nothing worked for me. Link to the file here: PBIX I would very much appreciate any help. Thank you very much in advance.
Reconcilation of values between parent and child -Calculated column
Hi, Request your support on formulating the correct DAX formula for caclulated column which calculates the value of sum of the children of that Node. There are more than five levels of parent ,child relation ship.Reconciliation need to happen for all parents with their immediate next level children. The generated table is as below My dax formula for the calculated column "Units_Expected" is not giving values correctly as its aggregating totally. In this sample only three levels are shown but acutal data has more levels. I request a generic formula for any number of levels. Units_Expected = if( Units_Reconciliation_Table[Node Type]="level1", sumx( FILTER(Units_Reconciliation_Table,PATHITEM(Units_Reconciliation_Table[Entity Path],2,INTEGER) =Units_Reconciliation_Table[Child Node ID]), Units_Reconciliation_Table[Units_Actuals]), ( if(Units_Reconciliation_Table[Node Type]="level2", sumx( FILTER(Units_Reconciliation_Table, AND( PATHITEM(Units_Reconciliation_Table[Entity Path],3,INTEGER) =Units_Reconciliation_Table[Child Node ID], PATHITEM(Units_Reconciliation_Table[Entity Path],2,INTEGER) =Units_Reconciliation_Table[Immediate_Parent])), Units_Reconciliation_Table[Units_Actuals]), if(Units_Reconciliation_Table[Node Type]="level3", sumx( FILTER(Units_Reconciliation_Table,PATHITEM(Units_Reconciliation_Table[Entity Path],4,INTEGER) =Units_Reconciliation_Table[Child Node ID]), Units_Reconciliation_Table[Units_Actuals]), if(Units_Reconciliation_Table[Node Type]="level4", sumx( FILTER(Units_Reconciliation_Table,PATHITEM(Units_Reconciliation_Table[Entity Path],5,INTEGER) =Units_Reconciliation_Table[Child Node ID]), Units_Reconciliation_Table[Units_Actuals]) ) ) ))) Appreciate your help . Thanks in advance.
Repeating values in PowerBI Hierarchy
Hello there, I am trying to create a hierarchy in PowerBI. There can be upto 7 levels in the hierarchy. So, I've defined Path and the 7 levels. However, there are items with less than 7 levels (some even 1). The Matrix table shows the same values of the parent till level 7. Is there a way to control this recursion? Attaching screenshots below. Thanks! Path DAX: Path length < 7 Level definition: Matrix Visual:
