Forum Discussion
Creating a new table based on multiple factors
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!
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-duplicate/td-p/132473Solution:
Table2 =
VAR T1 = SUMMARIZE('Table1'[TicketNumber];"First action"; MIN('Table1'[ActivityTime]))
VAR T2 = FILTER(NATURALINNERJOIN('Table1';T1); 'Table1'[TimeDifference] > 'Table1'[PriorityTimes])
RETURN T2Thanks v-yuta-msft for trying to help, you did put me on the right track!
4 Replies
Hi Ssmit,
Try DAX formula below:
new table =
FILTER (
table,
table[activityTime] = MIN ( table[activityTime] )
&& tabel[timeDifference] > table[priorityTime]
)Regards,
Jimmy Tao
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.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