Forum Discussion
Row Context in Calculated Column
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:
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,
Adam
A 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.
Thanks for such a quick response lbendlin.
Despite this method working in the test model, I suspected it wasn't the 'right' way of doing it.
It is a requirement that we are able to assign registrations to a project/team, so they can be counted and filted by team. We currently use a manual table based on staff members assigned to projects. But this has two problems staff members move around over time (and we have no clean data on this in the model) and further staff members often back fill for each other so the staff member isn't a great indicator of which project caused/ required the registration. We could maybe change our process to add a project drop down to the registration questions, but its often not clear at that point which project someone will attend first, and it wouldn't help with the historic data which we'd have to sort out some how.
Is there a way maybe to rewrite the measure, in the form of a calulated column with the correct filter context? The registration table rows contain a unique list of Contact IDs (i.e. each Contact ID has only one row - Contacts can only be registered once) and its that column I want to use to create the filter context in the Calulated Column - to provide the first project engaged for each Registration. Possible?
Thanks again, and sorry if this request went on a little while, and if I wasn't clear from the beginning. Cheers for continuing to engage.
Adam
staff members move around over time (and we have no clean data on this in the model)Maybe you want to fix that? Read about SCD2.
Thanks lbendlin
I've looked into slowly changing data previously (we don't use it in our model). Our working practices (we're a small community hub) are such that staff overlap between projects - for example our front desk does most (but not all) of the registrations and is assigned to a particuarly project - but staff from other projects will cover this role, say for the lunch break.
Therefore there isn't really a clean way of acheive what we need (I don't think) other than some sort of fomula to determine the first project a contact engaged with?
Thanks,
AdamJust for further clarity about our model, it isn't huge, which may help in terms of a solution.
We have:
Staff Members: 371 - including staff who have left - there's about 80 at any one time.
Contacts (people registered with us): 16,000
Projects: 127
Total Interactions: 198,598 - anything from an appointment, group session attendance, a referal, various kinds of outcome, and we also log enrollment start and finsh dates.
Most clients will only have a small number of interactions say 1-10, a small number will have maybe a few hundred. So a vitual table of the activity filtered by Client ID, wouldn't be very large for each client, but will need to be calulated 16,000 times.
So we're not talking millions of rows.
Thanks,
Adam