DAX Calculation find max value until certain date per employee (many-to-many)
Hello guys, i have a problem that is driving me nuts and I am coming to you for help. I have two tables: The first one details the working schedules per employee. It shows the schedule and the date it was submitted. The second one is an order table and it contains working orders, a date and the employee. I would like to get two new columns in the second table specifying the employee schedule. I specify columns as I need to do further calculations and I need to visualize the date without the employee field displayed. Even though any idea is welcome. Notice that both tables can have multiple times the same employee and the dates are not comparable! Example: Any recommendations? Thank you so much
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
Many to Many assistance or validation of approach
I'm working with data from Dynamics CRM and for now trying to relate Contacts with the campaign they interacted with, not just their source campaign. The way our instance is getting populated I believe my best recourse is to use the lead record's 'parentContactID' to relate the Contacts to the Lead (1 to M). The Campaigns (one) are related to the (many) Leads by the Lead's CampaignID. This essentially makes the Lead Table the bridge table between Campaigns and Contacts. I've concocted a measure that appears to give me the number I need but I'm would like your help in knowing two things. Is this measure the appropriate way to identify number of contacts that interacted with a campaign Would it be better to have a separate bridge table (essentially a subset of columns from the Lead table) instead of relying on the Lead table itself. I've below is a subset of CRM data that I'm using to work with. as well as a screenshot of the relationships using the Leads table as the bridge table. Appreciate your assistance! My Measures: Campaign Contacts = VAR CampaignExistence = COUNTROWS( FILTER( 'Contacts_v2 Inquiry', COUNTROWS(RELATEDTABLE('Leads_v2 Inquiry')) ) ) RETURN CampaignExistence Campaign Leads = CALCULATE( COUNTROWS( 'Leads_v2 Inquiry' ) ) Contacts contactId Created By Created On Description originatingLeadId 1 CRM Service 6/9/2024 0:02 Project Name: #1 4 2 CRM Service 6/3/2024 10:00 Project Name: #2 3 Former Employee 2/23/2018 10:44 Inquiry: Hello- #3 2 4 CRM Service 6/3/2024 10:00 Project Name: #4 5 Former Employee 11/30/2017 9:22 Campaigns campaignid Name 1 Campaign 1 2 Campaign 2 3 Campaign 3 4 Campaign 4 5 Campaign 5 6 Campaign 6 7 Campaign 7 8 Campaign 8 9 Campaign 9 10 Campaign 10 11 Campaign 11 12 Campaign 12 13 Campaign 13 14 Campaign 14 15 Campaign 15 16 Campaign 16 17 Campaign 17 18 Campaign 18 19 Campaign 19 campaignId Description Full Name leadId parentAccountId parentContactId Project Name: #1 Lead 1 1 Inquiry: Hello- #1 Lead 2 2 1 3 18 Project Name:#2 Lead 3 3 4 18 Project Name: #3 Lead 4 4 1 # 4 Lead 5 5 18 Project Name: #5 Lead 1 6 2 #6 Lead 6 7 #7 Lead 7 8 18 Project Name: #8 Lead 1 9 2 Hi, #9 Lead 8 10Suitable DAX for model having two fact tables and multiple dim tables
Hi, Requesting help on suitable DAX for creating matrix visual as below using two fact tables. FactTable1 having ReasonCode ingested at various time windows for several devices FactTable2 having Value ingested at various time stamps for several meters DimTable2 has the description for Reasons There is a bridge table DimTable1 mapping deviceID and meterID and has the description of machine name as well. Also there are two Dim tables one for Date (DimTable3) and another for time (DimTable4). Data model proposed as below.(PBIX file also enclosed) Required basic matrix visual giving aggregated total of values (FactTable2) output sliced based on date window and machine name as below: I had used calculated columns and got the output but since data size is huge it takes processing time and gets hanged. I need a suitable DAX which will aggregate based on relation ship only for my requirement.
