Calculated Column with Last Date from another table filtered

[tom_malkiewicz]

Hi all, 

I have two tables:

Volunteers:

ID | ... | Last Action Date

Volunteer Actions:

ID | Volunteer ID | Action Type | Action Date

I want to put the last Action Date from Volunteer Actions into Volunteers Table but only for specific Action Type

I have tried:

Last Vetting = CALCULATE(LASTDATE('Volunteer Actions'[Action Date]),FILTER('Volunteer Actions', 'Volunteer Actions'[Type_ID] = 8343)) but it gives me the same date for all records. I am assuming I am missing row context somewher but have no idea how to approach this. Should I create a Variable? 

Any help appreciated

Cheers

Tom

[AllisonKennedy · Accepted solution]

@tom_malkiewicz  As soon as you put CALCULATE in a calculated column, it essentially turns it into a measure context.

I am assuming that the relationship between these two tables is a 1 to many with single cross filter direction? Because of that you can't get the columns from the Volunteer Actions table into Volunteers table without changing the cross filter direction to both with I don't necessarily recommend. 

Is there a reason you need this created as a calculated column and can't just create the matrix or table visual using the aggregation LATEST on Action Date?

When do you want to filter for the action type? I'm going to assume you want to pull the latest date for that action type, rather than latest date for all actions. 

You can use SUMMARIZECOLUMNS to get that aggregation into the data model if needed, try this: 

As CALCULATED TABLE

Latest Date for 8343 Action Table = SUMMARIZECOLUMNS('Volunteer Actions'[Volunteer ID], FILTER('Volunteer Actions','Volunteer Actions'[Type_ID]<"8343"),"Latest Action", MAX('Volunteer Actions'[Date]))

Relate the Volunteer ID in Latest Action Table to Volunteer ID in Volunteers table.

Then add new CALCULATED COLUMN in Volunteers table; 

Last 8343 Action Date = RELATED('Latest Date for 8343 Action Table'[Latest Action])

[tom_malkiewicz]

Hi @AllisonKennedy 

Calculated table worked. Thank you

I created two - 1 for Max date and one for Min date. Is there a way to have both columns in one calculated table? I mean Volunteer ID and Earliest Date and Latest Date? 

All the best 

Tom

[AllisonKennedy]

Try this, and sorry my formula above should have had =8343 not <8343 so I have updated that as well:
Latest Date for 8343 Action Table = SUMMARIZECOLUMNS('Volunteer Actions'[Volunteer ID], FILTER('Volunteer Actions','Volunteer Actions'[Type_ID]="8343"),"Latest Action", MAX('Volunteer Actions'[Date]), "Earliest Action", MIN('Volunteer Actions'[Date]))

[tom_malkiewicz]

@AllisonKennedy  Brilliant. Thank you. 

[amitchandak]

@tom_malkiewicz ,

The one you create is it a measure or column ?

you can move data from one table to another like these examples

All are new columns

Item Name = RELATED('item'[Brand])

City Name = maxx(FILTER(geography,geography[City Id]=Sales[City Id]),geography[City]) // error for datatype

Month Name = LOOKUPVALUE('date'[Month Year],'date'[Date],Sales[Sales Date])

[tom_malkiewicz]

@amitchandak 

I want to created a calculated column with a max date from another table but only max date of specific subset of dates (Filtered by action type)

I hope I am making sense

EDIT

So I tried after your adivse to use MAXX

Last Vetting = MAXX(FILTER('Volunteer Actions','Volunteer Actions'[Type_ID] in{11409,11405,8338,8343,8349}),'Volunteer Actions'[Action Date])

But again I am getting the same date in all 30k rows