Find Groups of Data in One Table Just Before Data Point in Second Table

[torreyabl]

I am working on a project to help identify the root cause of errors in a physical process. To do this, I have two different data sources.

1. Timestamped list of all actions (large dataset, 250K rows), sample below

reftime	ActionLinkID
6/8/2022 10:19	000002883-000110065
6/8/2022 10:19	000002883-000110067
6/8/2022 10:19	000002883-000110066
6/8/2022 10:19	000002883-000110068
6/8/2022 10:21	000002883-000110069
6/8/2022 10:21	000002883-000110070
6/8/2022 10:21	000002883-000110071
6/8/2022 10:21	000002883-000110072
6/8/2022 10:21	000002883-000110073
6/8/2022 10:21	000002883-000110074
6/8/2022 10:23	000002883-000110075
6/8/2022 10:23	000002883-000110076

2. Timestamped list of all errors (relatively small dataset, 1K rows), sample below

reftime	error_id
6/8/2022 10:21	1598
6/8/2022 10:31	1599

To start, I consolidated them into a single table with a shared time-date reference. A subset of the result is the below:

reftime	error_id	ActionLinkID
6/8/2022 10:19		000002883-000110065
6/8/2022 10:19		000002883-000110067
6/8/2022 10:19		000002883-000110066
6/8/2022 10:19		000002883-000110068
6/8/2022 10:21		000002883-000110069
6/8/2022 10:21		000002883-000110070
6/8/2022 10:21		000002883-000110071
6/8/2022 10:21	1598
6/8/2022 10:21		000002883-000110072
6/8/2022 10:21		000002883-000110073
6/8/2022 10:21		000002883-000110074
6/8/2022 10:23		000002883-000110075
6/8/2022 10:23		000002883-000110076

I'd like to be able to filter the list of activities by all activities coming within n minutes of an error happening. The end result would be something like:

reftime	error_id	ActionLinkID
6/8/2022 10:21		000002883-000110069
6/8/2022 10:21		000002883-000110070
6/8/2022 10:21		000002883-000110071
6/8/2022 10:21	1598

My concept is, for each Action, to add a calculation that shows the time until the next error. Then I could filter that column easily.

reftime	error_id	ActionLinkID	Until Next Error
6/8/2022 10:19		000002883-000110065	01:15.0
6/8/2022 10:19		000002883-000110067	01:14.0
6/8/2022 10:19		000002883-000110066	01:14.0
6/8/2022 10:19		000002883-000110068	01:13.0
6/8/2022 10:21		000002883-000110069	00:08.0
6/8/2022 10:21		000002883-000110070	00:07.0
6/8/2022 10:21		000002883-000110071	00:07.0
6/8/2022 10:21	1598
6/8/2022 10:21		000002883-000110072	10:25.0
6/8/2022 10:21		000002883-000110073	10:18.0
6/8/2022 10:21		000002883-000110074	10:18.0
6/8/2022 10:23		000002883-000110075	07:57.0
6/8/2022 10:23		000002883-000110076	07:46.0
… more data here …
6/8/2022 10:31	1599

Unfortunately, I haven't been able to figure out a way to do that calculation in DAX or Power Query (seems like it could be better to do process upfront as data is static and computation cost may be high). I'd appreciate any suggestions that anyone might have. I'm also open to alternate approaches to solving the problem of finding "near records".

Thanks in advance,

Torrey

[speedramps]

Remove the slicer and add the error id and above measure to the same visual. The row context will then apply to each error id.

• Remember Super Users on this forum are unpaid volunteers, not Microsoft employees. I am happy to share knowledge free for kudos but understandably on an one question per ticket basis. 

The original question has been answered. Please show you appreciation in the usual way by clicking the thumbs up and accept as solution button. Thank you.

If you raise one ticket per problem you will get quicker replies from solvers, and each one gets the kudos they deserve.

[torreyabl]

Unfortunately, adding the Error ID and the DAX measure to the same visual (a table) does not allow selecting more than one error. I may be misunderstanding your instructions.

I disagree that my original question has been answered as I cannot yet "filter the list of activities by all activities coming within n minutes of an error happening".

I appreciate the help and fully understand that you are not paid Microsoft employees.

Thanks,

Torrey

[Ashish_Mathur]

Hi,

Explain very clearly how you got the results in the Until Next error column?  ( I assume that is what you want to calculate)

[torreyabl]

Ashish,

In order to get the "Until Next Error" value for each action row, I took the action-reftime value and looked for the next error row with an error-reftime value greater than the action-reftime value (assumes error rows are sorted by error-reftime value going from older to newest). Then I subtracted that action-reftime value from that error-reftime value to get the "Until Next Error" value. Let me know if that is not clear enough.

Thanks,

Torrey

[Ashish_Mathur]

Hi,

I still cannot understand.  Please share an MS Excel file with formulas there so that i can understan the logic of your calculation.

[torreyabl]

Ashish,

I have shared an Excel file at the location below with data and formulas that demonstrate the concept. I used a helper column to help arrive at the final calculation.

https://docs.google.com/spreadsheets/d/17r6_IvapRLFdHGjZSITVTNuWXyou8vC9/edit?usp=sharing&ouid=10478...

Thanks,

Torrey

[Ashish_Mathur]

Hi,

You may download my PBI file from here.

Hope this helps.

[torreyabl]

Ashish,

Thank you for this example. This seems to work very well and close to what I had in mind. I'll need to work on my understanding the use of EARLIER, but it seems to do the job (see formula below). I've put the key formula below. I assume that the addition of an INDEX colum in the appended table is needed after the RefTime sort to make this work, is that correct?

Until next error =
IF (
    Append1[ActionLinkID] = BLANK (),
    BLANK (),
    (
        CALCULATE (
            MIN ( Append1[Ref time] ),
            FILTER ( Append1, Append1[ErrorID] = EARLIER ( Append1[Next error ID] ) )
        ) - Append1[Ref time]
    ) * 24 * 60
)

Thanks,

Torrey

[Ashish_Mathur]

You are welcome.  If my previous reply helped, please mark that reply as Answer.

[torreyabl]

Ashish,

Could you outline your solution a bit in a message post and I can mark that as the Solution? If I only mark the file download message as the Solution, I'm not sure it would be helpful to others in the long run (esp. if the file gets deleted or moved at some point).

Thanks,

Torrey

[torreyabl]

I apologize for the delay as I was called away over the weekend, I will get you something by tomorrow.

Thanks,

Torrey

[speedramps]

Hi torreyabl

Thanks for your question !

Try this ...

Create a sepreate error and action tables. Dont merge them and dont add relationships.

Edit the Errors table in Power Query and add this column

Start datetime = [reftime] - #duration(0, 0, 9, 0)

This will subtract 9 minutes to give a error start datetime
note the syntax  is #duration(days, hours, minutes, seconds)
if you want to change 9 mins to your own value.

Change the Start datetime data type to date/time

Now add this dax measure ....

IsActionValid =

VAR errorstart = SELECTEDVALUE(Errors[Start datetime])

VAR errorend = SELECTEDVALUE(Errors[reftime])

VAR validactions =

FILTER(Actions,

Actions[reftime] >= errorstart &&

Actions[reftime] <= errorend )

RETURN

INT(NOT(ISEMPTY(validactions)))

 

This measure will return "1" for actions that occur 9 mins before a selected action.

The Isempty command will be False if the actions record is valid,

so we use the NOT command to reverse the False to a True.

Then use INT toconvert the True to 1 integer so we can sum and use it is slicers more easily.

 

Drag a table or slicer visual to your canvas, give it the title "errors" and add columns

Errors[error_id]

Errors[reftime]

Errors[Start datetime]

Drag another table visual to your canvas, give it the title "valid actions" and add columns

Actions[reftime]

Actions[ActionLinkID]

Add this filter to the "valid actions" table visual

IsActionValid = 1

 

Voila!  When you click on error, only the actions in the last 9 minutes will be displayed.

 

Hope you learnt some good DAX there because I prefer to explain and teach DAX rather than just give a solution

 

Thanks for reaching out for help.

I have helped you, now please help me by giving kudos.

Remeber we are unpaid volunteers.

Click the thumbs up and accept as solution button. 

One question per ticket please. If you need to extend your request then please raise a new ticket.

You will get a quicker response and each volunteer solver will get the kudos they deserve. Thank you !

[torreyabl]

Speedramps,

Thank you for your thorough explanation and teaching approach. I managed to implement what you proposed pretty easily. It works as described. It is possible that there may be a downside in that an Error has to be selected to see the corresponding action data and I can only see one Error of data at a time. We may be able to work around that.

One other thing I noticed was in trying to bring a static field from a linked Table into the Actions table visualization. I could use most fields without issue, but one long text field does not seem to want to work. If I add it to the visual, I just get a circling symbol at the top left of the visual. I use the field in other places, so somehow seems tied to the logic in this case, but I can't understand why. Any insights?

Thanks again,

Torrey

[speedramps · Accepted solution]

Thanks sorry for the lovely feedback. Please take the thumbs up button. 

If you add the error id and the above measure to the same table visual or matrix visual then you will be able to report multiple errors, because the SELECTEDVALUE command will get the relevant error id for each row.

The FILTER command is slow because it has to scan multiple action records. So it will perform better if you use a slicer to pick just a few errors.

Long strings may exceed your RAM  memory.

Users rarely need to see the long text for all rows. 

So consider just displaying the action main details and then adding a tool tip or  drillthrough option so a user can click on an action and view the individual long text.

This method works really well and looks good on screen.  I often use it for actions.Sometimes I include the first 40 characters of the long text in the list of the main details. But if the user wants to see more then they can click the tooltip  or drillthrough to see the full long text.

Please accept the solution and raise a new ticket, rather than keep adding more questions here. Thanks.

You are getting 3 answers for the prices of 1 kudos ;-(

[torreyabl]

Unfortunately, if I select more than one Error in the table or by using a Slicer, it blanks out all of the matched actions. If I'm understanding properly, that might be because SELECTEDVALUE returns blank if there is more than one item selected (so the date compares will always fail).

In terms of the text question, I didn't know if it was related to the solution that you provided me as I hadn't encountered the issue elsewhere.

In terms of marking a solution, covering actions across multiple errors was part of my initial question, so I was hoping to solve that completely before marking complete.

Thanks,

Torrey

[speedramps]

Ok so you added the error id and measure to the same table visual and the SELECTEDVALUE gets the row context ok. This will collate the actions by error code.

Now also add a slicer for error id with multiple selection turned on.

Select a few errors and the visual will collate the actions by error id.

[torreyabl]

speedramps,

I think that may have gotten this sorted out by starting with a new page. The data set is large, so response is slow, but it is looking better now on the selection of multiple Errors. I will continue to test a bit and let you know.

Thanks,

Torrey

[torreyabl]

I apologize for the delay as I was called away over the weekend, I will get you something by tomorrow.

Thanks,

Torrey