Forum Discussion

Ssmit's avatar
Ssmit
Frequent Visitor
8 years ago
Solved

Filtering based on the earliest known activityTime date per ticket

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:

ticketNumbercreationTimeactivityTimepriorityLevelpriorityTimestatusNiveautypeIdLeveltimeDifference
714-02-2018 13:32:2921-02-2018 13:41:36Average08:00:00ActiveMalfunction46:09
714-02-2018 13:32:2921-02-2018 16:28:56Average08:00:00ActiveMalfunction75:56
607-03-2017 09:28:2807-03-2017 11:32:00High02:00:00ActiveMalfunction02:04
607-03-2017 09:28:2807-03-2017 14:24:31High02:00:00ActiveMalfunction04:56
528-09-2016 17:17:4018-04-2018 09:04:15Low24:00:00ActiveMalfunction2546:55
528-09-2016 17:17:4018-04-2018 09:10:15Low24:00:00ActiveMalfunction2556:55
428-12-2015 15:23:4318-04-2018 09:04:15Average08:00:00ActiveMalfunction5285:40
428-12-2015 15:23:4318-04-2018 09:10:15Average08:00:00ActiveMalfunction5291:40
222-11-2015 08:30:0022-11-2015 10:30:00Urgent01:00:00OpenMalfunction02:00

 

What I want it to look like:

ticketNumbercreationTimeactivityTimepriorityLevelpriorityTimestatusNiveautypeIdLeveltimeDifference
714-02-2018 13:32:2921-02-2018 13:41:36Average08:00:00ActiveMalfunction46:09
607-03-2017 09:28:2807-03-2017 11:32:00High02:00:00ActiveMalfunction02:04
528-09-2016 17:17:4018-04-2018 09:04:15Low24:00:00ActiveMalfunction2546:55
428-12-2015 15:23:4318-04-2018 09:04:15Average08:00:00ActiveMalfunction5285:40
222-11-2015 08:30:0022-11-2015 10:30:00Urgent01:00:00OpenMalfunction02: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?

Need Help
  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    8 years ago

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

3 Replies