topic Re: Help to get next timestamp from first timestamp in DAX Commands and Tips

Help to get next timestamp from first timestamp

Anonymous — Fri, 16 Apr 2021 15:47:07 GMT

Hi guys,

I am trying to get the pairs of "00 Initialization: Assigning all transaction item variables" and "Transaction number X was successfully processed.", and their time stamps. There might be a record of "00 Initialization: Assigning all transaction item variables" in between, that should be ignored. In the same day and for the same JobKey, I need to find the timestamp from the first instance of A to the first instance of B. In a day, there might be more than 1 pair, as indicated by the highlighted-blue (I need those time stamp too).

A: 00 Initialization: Assigning all transaction item variables

B: Transaction number X was successfully processed.

link to file: https://drive.google.com/file/d/1guGmTxp8Z2lhxMWTrxmf7pnhrRDQxTyR/view?usp=sharing

Table 1

00 Initialization: Assigning all transaction item variables	2/26/21 2:55 AM
00 Initialization: Transaction type: Permits. Performing Local Council Permit Renewals	2/26/21 2:55 AM
10 Initialize Matrix Tables: Initializing config variables to be used for processing	2/26/21 2:55 AM
20 Split Submissions Entry: Navigating to Invoices to Split	2/26/21 2:55 AM
20 Split Submissions Entry: Finding submission entry to split	2/26/21 2:55 AM
20 Split Submissions Entry: Submission to split found	2/26/21 2:55 AM
........
00 Initialization: Transaction type: Permits. Performing Local Council Permit Renewals	2/26/21 2:58 AM
.......
Transaction number 2 was successfully processed.	2/26/21 3:03 AM
.......
00 Initialization: Assigning all transaction item variables	2/26/21 3:03 AM
...........
Transaction number 3 was successfully processed.	2/26/21 3:06 AM

I have tried with the following DAX, but NP AHT GetFirstAssignAllTransactionItemVariables only returns the first timestamp of A (belonging to the red pair). I have inserted the link to the excel with all the column headers. Assistance is much appreciated.

NP AHT GetFirstAssignAllTransactionItemVariables =

MINX(

FILTER(

Logs,

Logs[ProcessName] = "RPANET009.Esker.Payments.Management.Performer_RPANET009 Application, Payments Management of Local Council Permits"

&& Logs[JobKey] = EARLIER(Logs[JobKey])

&& Logs[TimeStamp] < EARLIER(Logs[TimeStamp])

&& SEARCH("00 Initialization: Assigning all transaction item variables", [Message],,BLANK())>0

Logs[TimeStamp]

)

NP AHT GetNextSuccessfulProcessed =

MINX(

FILTER(

Logs,

Logs[ProcessName] = "RPANET009.Esker.Payments.Management.Performer_RPANET009 Application, Payments Management of Local Council Permits"

&& Logs[JobKey] = EARLIER(Logs[JobKey])

&& Logs[TimeStamp] >= EARLIER(Logs[TimeStamp])

&& SEARCH("was successfully processed.", [Message],,BLANK())>0

Logs[TimeStamp]

)

Re: Help to get next timestamp from first timestamp

AlB — Sat, 17 Apr 2021 19:11:55 GMT

Hi Anonymous

You can do this in the query editor. This would be the M code that reads the data from your Excel file and extracts the time stamps you need. See it all at work in the attached file.

let Source = Excel.Workbook(File.Contents("d:\_system\Desktop\Forum\dashboard_network_permits_getAHT2.xlsx"), null, true), Table1_Table = Source{[Item="Table1",Kind="Table"]}[Data], #"Changed Type" = Table.TransformColumnTypes(Table1_Table,{{"ID", Int64.Type}, {"SD", type datetime}, {"ED", type datetime}, {"JobKey", type text}, {"Source", Int64.Type}, {"Message", type text}, {"TimeStamp", type datetime}, {"ProcessName", type text}}), #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([Message] = "00 Initialization: Assigning all transaction item variables" or (Text.Contains([Message], "Transaction number") and Text.Contains([Message], "was successfully processed")))), #"Added Custom" = Table.AddColumn(#"Filtered Rows", "DayStamp", each Date.From([TimeStamp]), type date), #"Sorted Rows" = Table.Sort(#"Added Custom",{{"TimeStamp", Order.Ascending}}), #"Grouped Rows" = Table.Group(#"Sorted Rows", {"JobKey", "DayStamp", "Message"}, {{"TimeStamp", each List.Min([TimeStamp]), type datetime }}, GroupKind.Local) in #"Grouped Rows"

Please accept the solution when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Re: Help to get next timestamp from first timestamp

Anonymous — Sun, 18 Apr 2021 10:29:45 GMT

hi AlB ,

Tx. for your quick response! Is is possible to get the solution in DAX?

figure 1 - correct pairing of A and B using DAX NP AHT GetFirstAssignAllTransactionItemVariables and NP AHT GetNextSuccessfulProcessed, from 2/26/2021 2:55 AM to 2/26/2021 3:03 AM.

figure 2 - Incorrect pairing of A and B using DAX NP AHT GetFirstAssignAllTransactionItemVariables and NP AHT GetNextSuccessfulProcessed, from 2/26/2021 2:55 AM (supposed to be 2/26/21 3:03 AM ) to 2/26/2021 3:06 AM.

Re: Help to get next timestamp from first timestamp

AlB — Sun, 18 Apr 2021 22:24:03 GMT

Anonymous

It is, but I'd certainly recommend to do it in PQ. Way simpler. Create a new calculated table. See attached file

NewTable = VAR baseT_ = FILTER ( SUMMARIZE ( Table1, Table1[JobKey], Table1[DayStamp], Table1[Message], Table1[TimeStamp] ), Table1[Message] = "00 Initialization: Assigning all transaction item variables" || ( FIND ( "Transaction number", [Message], 1, 0 ) > 0 && FIND ( "was successfully processed", [Message], 1, 0 ) ) ) RETURN FILTER ( baseT_, VAR previousTrans_ = MAXX ( FILTER ( baseT_, [TimeStamp] < EARLIER ( [TimeStamp] ) && FIND ( "Transaction number", [Message], 1, 0 ) > 0 && FIND ( "was successfully processed", [Message], 1, 0 ) > 0 && [DayStamp] = EARLIER ( [DayStamp] ) ), [TimeStamp] ) VAR previousIni_ = MAXX ( FILTER ( baseT_, [TimeStamp] < EARLIER ( [TimeStamp] ) && [Message] = "00 Initialization: Assigning all transaction item variables" && [DayStamp] = EARLIER ( [DayStamp] ) ), [TimeStamp] ) RETURN IF ( [Message] = "00 Initialization: Assigning all transaction item variables", previousIni_ <= previousTrans_, TRUE () ) )

Please accept the solution when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Re: Help to get next timestamp from first timestamp

Anonymous — Tue, 20 Apr 2021 09:40:56 GMT

Thanks AlB! Just applied the solution to my company's real-data and it worked like a charm! 5 stars to you my friend!