Creating a new table based on multiple factors

[Ssmit]

Greetings,

I'm still quite new to PowerBI and I've had the luck of pretty much finding information about everything I needed.

However; my issue is as follows:

I need to create a new table based on multiple factors.

First being I need to select the earliest possible ticket based on the earliest action date/time(activityTime) that is known.
Second I need to filter the above by checking if the timeDifference is bigger than the priorityTime and only display the tickets that meet both of the above factors.

companyName	title	ticketNumber	cTime	activityTime	priorityLevel	priorityTime	userFullname	statusNiveau	typeIdLevel	timeDifference
x	test	10	17-04-2018 09:23:39	18-04-2018 09:04:15	Low	24:00:00	x	Open	Malfunction	08:40
x	test	9	16-04-2018 16:12:31	16-04-2018 17:02:29	Low	24:00:00	x	Open	Malfunction	00:49
x	test	8	20-02-2018 14:28:33	20-02-2018 14:44:07	Low	24:00:00	x	Active	Malfunction	00:15
x	test	8	20-02-2018 14:28:33	20-02-2018 15:12:22	Low	24:00:00	x	Active	Malfunction	00:43
x	test	8	20-02-2018 14:28:33	20-02-2018 15:58:13	Low	24:00:00	x	Active	Malfunction	01:29
x	test	8	20-02-2018 14:28:33	21-02-2018 10:05:54	Low	24:00:00	x	Active	Malfunction	04:37
x	test	7	14-02-2018 13:32:29	21-02-2018 13:41:36	Average	08:00:00	x	Active	Malfunction	46:09
x	test	7	14-02-2018 13:32:29	21-02-2018 16:28:56	Average	08:00:00	x	Active	Malfunction	75:56
x	test	6	07-03-2017 09:28:28	07-03-2017 11:32:00	High	02:00:00	x	Active	Malfunction	02:04
x	test	6	07-03-2017 09:28:28	07-03-2017 14:24:31	High	02:00:00	x	Active	Malfunction	04:56
x	test	5	28-09-2016 17:17:40	18-04-2018 09:04:15	Low	24:00:00	x	Active	Malfunction	2546:55
x	test	5	28-09-2016 17:17:40	18-04-2018 09:10:15	Low	24:00:00	x	Active	Malfunction	2556:55
x	test	4	28-12-2015 15:23:43	18-04-2018 09:04:15	Average	08:00:00	x	Active	Malfunction	5285:40
x	test	4	28-12-2015 15:23:43	18-04-2018 09:10:15	Average	08:00:00	x	Active	Malfunction	5291:40
x	test	3	23-11-2015 09:15:00	23-11-2015 09:15:00	Urgent	01:00:00	x	Open	Malfunction	00:00
x	test	2	22-11-2015 08:30:00	22-11-2015 10:30:00	Urgent	01:00:00	x	Open	Malfunction	02:00
x	test	1	21-11-2015 09:30:00	21-11-2015 10:30:00	High	02:00:00	x	Active	Malfunction	01:00

What is shown above is the current test data and what is shown below is what the new table should look like based on the two factors but I have absolutely no clue where to start with this

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

A massive thank you to whomever is able to help, even if it's just a little bit!

[v-yuta-msft]

Hi Ssmit,

Try DAX formula below:

new table =
FILTER (
    table,
    table[activityTime] = MIN ( table[activityTime] )
        && tabel[timeDifference] > table[priorityTime]
)

Regards,

Jimmy Tao

[Ssmit]

Hello Jimmy,

Unfortunately this returns an empty table.

I think the reason for this is the way I put together the priorityTime and TimeDifference.

Both are in text format because I wanted both of them to show the full time value instead of starting over when the time goes past 23:59. I'm using CONCATENATE and TRUNC to piece them together.

[Ssmit]

Decided to keep playing around with it for a bit after I was done with some other stuff I had to do today.

timeDifference > priorityTime works just fine, I'm guessing that the other part of the filter Jimmy suggested somehow didn't get picked up when making the new table

[Ssmit · Accepted solution]

Happened to find a new source that allowed me to achieve what I needed to achieve:
https://community.powerbi.com/t5/Desktop/Filter-Query-or-create-new-table-based-on-first-date-duplic...

Solution:

Table2 =
VAR T1 = SUMMARIZE('Table1'[TicketNumber];"First action"; MIN('Table1'[ActivityTime]))
VAR T2 = FILTER(NATURALINNERJOIN('Table1';T1); 'Table1'[TimeDifference] > 'Table1'[PriorityTimes])
RETURN T2

Thanks v-yuta-msft for trying to help, you did put me on the right track!