Forum Discussion
Row Context in Calculated Column
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 with
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.
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
Apoligies to be a bit quiet on this. I've made some progress which works but I'm left with two registration tables making the model a bit messy.
The file for the update model is here:
One problem with my previous test model is that I forgot to correctly format the Date column in the Activities table as a Date - it was formatted as a text field.
I've also added a new measure which now seems to be working and providing the right output:
First Project Attended 3 =
CALCULATE (
MINX ( TOPN ( 1, 'Activity Table', [Date], ASC ), 'Activity Table'[Project ID] ),
REMOVEFILTERS ( 'Calendar' )
)I've managed to create a new calulated table using ADDCOLUMNS as follows:
Registrations Project Connection =CALCULATETABLE(ADDCOLUMNS(Registrations, "Project ID", [First Project Attended 3]), USERELATIONSHIP (Registrations[Contact ID], 'Activity Table'[Contact ID]))The "Project ID" column here is now the first project attended, and I can use this column as a relationship with project , and then count the registrations by Team. Its all giving the expected result.However as I said above, it seems messy with now two tables, which might also impact on performance?
A cleaner method would seem to be using the measure somehow in a calculated column and doing away with the new calculated table - I can't seem to make that work. I've added the column to 'Registrations' but I can't make a Relationship with the project table as it creates a 'Circular Dependancy'.
Any thoughts would be appreacited.
I think I do need this new relationships to be in the Registrations table which is a fact table, like Activities. The Contact table in my main model was causings issues because it had too many relationships connected to it and to the Date table. I resolved this by creating the new Registrations table and treating that as a fact table with date of registrations being held there. Makes sense to me to then link the registrations to the first project, and I don't really want to connect two Dimension tables - Contact and Project. I think that might cause issues. But happy to be steered in a different direction.
Many thanks,
AdamA cleaner method would seem to be using the measure somehow in a calculated columnThat is not something you can meaningfully do in Power BI. You cannot/should not create calculated tables or columns from measures, since the filter context will be meaningless.
Take a step back. Write down the business problem(s) you are trying to solve. Model your data model according to the problem, and worst case have separate models if the problems are too different.
Once the model is sorted the visualizations will be much easier.