Forum Discussion
Calculated Column with Last Date from another table filtered
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 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
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
- 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]))AllisonKennedy Brilliant. Thank you.
