topic Re: Finding most recent task of a certain type on Table B for a case in table A. in DAX Commands and Tips

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

Singularity9 — Wed, 26 Apr 2023 16:27:27 GMT

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

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

tamerj1 — Wed, 26 Apr 2023 17:19:16 GMT

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

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

Singularity9 — Thu, 27 Apr 2023 11:28:36 GMT

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."

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

tamerj1 — Thu, 27 Apr 2023 15:30:07 GMT

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

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

Singularity9 — Thu, 27 Apr 2023 15:49:03 GMT

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