most recent
1 Topic
Finding most recent task of a certain type on Table B for a case in table A.
Hi. I have two tables, Case and Case Task History. A has a list of cases. B has a list of tasks performed for cases. They are linked via case ref, on a a both-ways one to many relationship. I want to create a column on the Case table which will tell me the most recent task for each case on Case Task History. However I only want it to look for 4 types of task - let's call them Type A, Type B, Type C and Type D. All others should be ignored. So for example if we have this: 01/02/2023 Type A 02/02/2023 Type C 03/02/2023 Type H I would want the formula to return "Type C". Below is the Dax I'm using for this. It doesn't work. As far as I can tell it's returning the most recent task of any type, and if that's not one of the types I specify, it just returns a blank. So for the above example, it would return a blank, because Type H is the most recent task. Does anyone know how to fix this? Thanks in advance. Latest Task = var _caseref = 'CASE'[Case Ref] VAR _maxdate = CALCULATE ( MAX ( 'CASE TASK HISTORY'[GTH Performed Datetime] ), FILTER ( 'CASE TASK HISTORY', 'CASE TASK HISTORY'[Case Ref] = _caseref ) ) VAR _mostrecentsubmissiontask = CALCULATE ( max ( 'CASE TASK HISTORY'[GTH Task Name] ), FILTER ( 'CASE TASK HISTORY', 'CASE TASK HISTORY'[Case Ref] = _caseref && 'CASE TASK HISTORY'[GTH Task Name] IN { "Type A", "Type B", "Type C", "Type D" } && 'CASE TASK HISTORY'[GTH Performed Datetime] = _maxdate ) ) RETURN _mostrecentsubmissiontaskSolved952Views0likes4Comments