Filtering based on the earliest known activityTime date per ticket

[Ssmit]

Dear all,

I'm trying to filter through tickets and I wanted to display the tickets where the activityTime is the earliest value and filter out everything else that has the same ticketNumber.

What the data currently looks like:

ticketNumber	creationTime	activityTime	priorityLevel	priorityTime	statusNiveau	typeIdLevel	timeDifference
7	14-02-2018 13:32:29	21-02-2018 13:41:36	Average	08:00:00	Active	Malfunction	46:09
7	14-02-2018 13:32:29	21-02-2018 16:28:56	Average	08:00:00	Active	Malfunction	75:56
6	07-03-2017 09:28:28	07-03-2017 11:32:00	High	02:00:00	Active	Malfunction	02:04
6	07-03-2017 09:28:28	07-03-2017 14:24:31	High	02:00:00	Active	Malfunction	04:56
5	28-09-2016 17:17:40	18-04-2018 09:04:15	Low	24:00:00	Active	Malfunction	2546:55
5	28-09-2016 17:17:40	18-04-2018 09:10:15	Low	24:00:00	Active	Malfunction	2556:55
4	28-12-2015 15:23:43	18-04-2018 09:04:15	Average	08:00:00	Active	Malfunction	5285:40
4	28-12-2015 15:23:43	18-04-2018 09:10:15	Average	08:00:00	Active	Malfunction	5291:40
2	22-11-2015 08:30:00	22-11-2015 10:30:00	Urgent	01:00:00	Open	Malfunction	02:00

What I want it to look like:

ticketNumber	creationTime	activityTime	priorityLevel	priorityTime	statusNiveau	typeIdLevel	timeDifference
7	14-02-2018 13:32:29	21-02-2018 13:41:36	Average	08:00:00	Active	Malfunction	46:09
6	07-03-2017 09:28:28	07-03-2017 11:32:00	High	02:00:00	Active	Malfunction	02:04
5	28-09-2016 17:17:40	18-04-2018 09:04:15	Low	24:00:00	Active	Malfunction	2546:55
4	28-12-2015 15:23:43	18-04-2018 09:04:15	Average	08:00:00	Active	Malfunction	5285:40
2	22-11-2015 08:30:00	22-11-2015 10:30:00	Urgent	01:00:00	Open	Malfunction	02:00

As you can see in the second table it only shows the tickets where the activityTime is the earliest.

What I've tried so far:
table2 =
VAR T1 = FILTER('table1';
'table1'[timeDifference] > 'table1'[priorityTimes]
&&
'table1'[TypeIdLevel] = "Malfunction"
&&
'table1'[activityTime] = MIN('table1'[activityTime]))
RETURN T1

In the formula that is displayed above you can see that I have already applied some filters. That last part however returns an empty table because it only collects the ticket where the activityTime is the absolute lowest value (which happens to be a ticket of a different typeIdLevel).

Is there another way to accomplish what I want to accomplish?

[Zubair_Muhammad · Accepted solution]

HI @Ssmit

Try this calculated Table

New Table =
VAR tickets =
    SELECTCOLUMNS (
        VALUES ( Table1[ticketNumber] ),
        "Ticket_Number", [ticketNumber]
    )
RETURN
    GENERATE (
        tickets,
        CALCULATETABLE ( TOPN ( 1, Table1, Table1[activityTime], ASC ) )
    )

[Zubair_Muhammad]

@Ssmit

Please see file attached

[Ssmit]

Zubair_Muhammad,

Thank you, this does exactly what I need it to do!

much appreciated!