Row Context in Calculated Column

[adamlang]

Hello,

I have a data model that looks like this:

Registrations contains a list of people, and the date they registered.

I want to be able to count Registrations filtering by the Team they relate to. The team is stored in 'pre_project' table and currently connects to the 'Merged Activity Table' through a relationship via the [ProjectID]. There is no such [ProjectID] in 'Registrations' so I need to add one. I can infer which Project did the registration by looking at the 'Merged Activity Table' and finding the first project each person engaged with, and using that [ProjectID].

So I need to add the [ProjectID] as a new column in the 'Registrations' table so I can create the relationship to the 'Pre_project' table, and then use the Team filter.

I'm having trouble creating the calulated column in Registrations, I think which relates to a mix of row context and relationships.

I have however created a measure which when added to a visualisation table with Contact[Contact ID] correctly returns the first project engaged with. The measure is:

First Project Attended =

MAXX(

    TOPN( 1,

    SUMMARIZE('Merged Activity Table', 'Calendar'[Date], 'pre_project'[pre_projectid]),

    CALCULATE( MIN ( 'Calendar'[Date] ) ), ASC ),

     'pre_project'[pre_projectid] )

I basically want to convert this code to use in a Calulated Column within the 'Relationships' table. I think I have to activate the inactive relationship between 'Merged Activity Table' and relationships, which is simple enough, but I think something is going wrong with the Row Context in the calulated column.

Here's the Dax I tried for the Calulated Column which is not working, which tries to call on the Measure above:

First Project Attended = CALCULATE([First Project Attended], USERELATIONSHIP('Merged Activity Table'[ContactID], Registrations[contactid]))

Any thoughts, I'm clearly getting it wrong somewhere, but just can't work it out - its been puzzling me for ages.

Many thanks,

Adam

[lbendlin]

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information. Do not include anything that is unrelated to the issue or question.

Need help uploading data? https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

[adamlang]

Hi @lbendlin,

Here's a link to a test model.

Test Data 1.pbix

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 

[lbendlin]

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.

[adamlang]

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

[lbendlin]

 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.

[adamlang]

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

[adamlang]

@lbendlin 

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:

Test Data 2.pbix 

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,

Adam

[lbendlin]

A cleaner method would seem to be using the measure somehow in a calculated column

That 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.

[adamlang]

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

[lbendlin]

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.

[adamlang]

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,

Adam

[adamlang]

@lbendlin 

Just 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