Forum Discussion

adamlang's avatar
adamlang
Helper III
1 year ago

Row Context in Calculated Column

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

 

DAX

12 Replies