Forum Discussion

tom_malkiewicz's avatar
tom_malkiewicz
Helper I
6 years ago
Solved

Calculated Column with Last Date from another table filtered

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 A...
Need Help
Tips and Tricks
  • AllisonKennedy's avatar
    AllisonKennedy
    6 years ago

    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])
     
     
     

     

     

AllisonKennedy's avatar
AllisonKennedy
Community Champion
6 years ago
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]))