Forum Discussion
Row Context in Calculated Column
Hi lbendlin,
Here's a link to a test model.
I thought the measure i'd written was working correctly, but actually its only close, but will sometimes give the wrong result. So I'm not as close to fixing this as I thought.
Thanks for any help or suggestions you can offer.
Adam
I want to be able to count Registrations filtering by the Team they relate to.
According to your data model registrations and teams are unrelated. Contact IDs can be members of multiple projects.
Thanks lbendlin,
Yes that's right there's currently no relationship between Registration and Teams. I want to create one by working out what the project each client first engaged with is and using that to establish a new realtionship using a calculated column.
There's only one person (5555-0008) whose first project was P-003 and whose registration should be linked with Team Bravo. I'm not sure why the measure Registrations is wrong, but its not giving the correct answer. The three other people who have engaged with project P-003 (5555-0001, 5555-0007 and 5555-0009) did so following/ after an engagement with either project P-001 or P-002 (based on the date in the Merged Activity table), therefore their registration needs to be linked to the first project they engaged with and to Team Alpha.
I ultimately need the code to be in the form of a calulated column returing for each registration the correct project ID, so that I can make a relationship with the project ID, in the Registrations Table.
Hope that makes sense.
Thanks for enaging with this. I appreciate it may not be straightforward.
Adam
I want to create one by working out what the project each client first engaged withI ultimately need the code to be in the form of a calulated column returing for each registration the correct project ID, so that I can make a relationship with the project ID, in the Registrations Table.No, you need to add that calulated column to the Contact table, not the registrations table.
Ah maybe the Coloumn should be in Contact (as a Dimention table) rather than (Registrations as a fact table) i'm sure that will work. In my model there's actually a one to one relationship between Contact and Registrations as they come from the same database table, but to make my model connect to the calendar table I needed to split out the Registrations (fact table, and the Created on Date). This involved forcing a one to many relationship between the tables.
But the measure is still not correct, I think its returning the most recent project (based on the max date?). The corrrect list on a manual check should be:
Created On Contact ID Created By First Project 01-May-23 5555-0001 Staff-001 P-001 01-May-23 5555-0002 Staff-002 P-002 04-Jul-23 5555-0003 Staff-003 P-001 06-Jul-23 5555-0004 Staff-001 P-001 03-Aug-23 5555-0005 Staff-002 P-001 09-Sep-23 5555-0006 Staff-003 P-001 04-Oct-23 5555-0007 Staff-001 P-002 15-Oct-23 5555-0008 Staff-002 P-003 16-Oct-23 5555-0009 Staff-003 P-001 01-Apr-24 5555-0010 Staff-001 P-002 Thanks again,
Adam