Finding most recent task of a certain type on Table B for a case in table A.

[Singularity9]

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

_mostrecentsubmissiontask

[tamerj1]

Hi @Singularity9 

Please try

Latest Task =
VAR CaseRef = 'CASE'[Case Ref]
VAR T1 =
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" }
)
VAR T2 =
TOPN ( T1, 'CASE TASK HISTORY'[GTH Performed Datetime] )
RETURN
MAXX ( T2, 'CASE TASK HISTORY'[GTH Task Name] )

[Singularity9]

Thanks for the help. I'm afraid this below part doesn't work, as the second argument in TOPN needs to be a table, not a column:

VAR T2 =
TOPN ( T1, 'CASE TASK HISTORY'[GTH Performed Datetime] )

When I change it to this:

VAR T2 =
TOPN ( T1, 'CASE TASK HISTORY', [GTH Performed Datetime])

...then it is no longer underlined as an error, but then when I run the formula I get "The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value." 

[tamerj1 · Accepted solution]

@Singularity9 
Apologies, that was a typo mistake, I was actually typing on my phone and missed the N argument of TOPN. Please try

Latest Task =
VAR CaseRef = 'CASE'[Case Ref]
VAR T1 =
    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" }
    )
VAR T2 =
    TOPN ( 1, T1, 'CASE TASK HISTORY'[GTH Performed Datetime] )
RETURN
    MAXX ( T2, 'CASE TASK HISTORY'[GTH Task Name] )

[Singularity9]

That did it! Thank you very much, that's incredibly helpful. Great job!