Extracting data from a FILTER() table based on data within the returned table

[jasmith866]

My dataset has a table of records representing completed tasks, and a separate table containing a log of 'history' events relating to those tasks. I am trying to extract, in a custom column for a given task, data from the history table that matches certain criteria.

Here's an example of what the history table looks like:

Parent Record	Field	Created By	Created Time	Value
132456789	Field 1	John	10/10/2010 10:10	VALUE A
132456789	Field 1	Jane	12/12/2012 12:12	VALUE A
132456789	Field 2	John	14/02/2015 14:14	VALUE C
132456789	Field 2	Jane	18/04/2017 16:16	VALUE D
132456789	Field 3	John	21/06/2019 18:18	VALUE E
132456789	Field 4	Jane	23/08/2019 20:20	VALUE F

The table contains a large number of rows, and on each row we have an identifier to link the history log back to the parent 'task' record that they relate to.

What I want to find for each task is:

The Name of the person who has an entry for a given field (say Field 1 in the example) with the earliest creation date

Here's what i've been working with so far:

CALCULATE(FIRSTNONBLANK('History Table'[CreatedBy],1),FILTER('History Table','History Table'[ParentId] = 'Task Table'[Id] && 'History Table'[Field] = "Field 1")

I wrote the above with the expectation that as the rows in the history table are in chronological order, the firstnonblank row would contain the earliest date. But i'm getting inconsistent results whereby the first row is not always selecting the earliest date - which suggests to me that the sorting on the returned table is not fixed and I can't find a resource anywhere which describes how the function works.

Looking for ideas/suggestions for how to tackle the problem. 1000 thanks in advance

[v-xicai · Accepted solution]

Hi @jasmith866 ,

You may create measure like DAX below.

Measure1 =
VAR MinDate =
    CALCULATE (
        MIN ( 'History Table'[Created Time] ),
        FILTER (
            ALLSELECTED ( 'History Table' ),
            'History Table'[ParentId] = MAX ( 'History Table'[ParentId] )
                && 'History Table'[Field] = MAX ( 'History Table'[Field] )
                && 'History Table'[Created Time] < MAX ( 'History Table'[Created Time] )
        )
    )
RETURN
    CALCULATE (
        FIRSTNONBLANK ( 'History Table'[CreatedBy], 1 ),
        FILTER (
            'History Table',
            'History Table'[ParentId] = 'Task Table'[Id]
                && 'History Table'[Field] = "Field 1"
                && 'History Table'[Created Time] = MinDate
        )
    )

Best Regards,

Amy 

Community Support Team _ Amy

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.