Forum Discussion

MuhammadP's avatar
MuhammadP
Frequent Visitor
3 years ago

Nearest Record Search based on Date

Hi,

 

I have a table of events with the structure below. Each event has a file number, an event code, user and date and some of them are exception events (based on some other logic that's on the IsExceptionEvent column). On this table, I need to create a calculated column called Responsible Party that, in case the event is an exception event, gets the user code from the nearest non-exception event.

 

Events Table Sample:

File NumberBusiness UnitEvent CodeEvent DateEvent User CodeFileEventIsExceptionEventResponsible Party
632701TEFVALA16/03/2023 11:52AOAS632701FVALATRUE 
632701TECBAEA15/03/2023 00:00AOVO632701CBAEATRUE 
632701TEFDRAA20/03/2023 15:45NOOA632701FDRAAFALSE 
632701TEFCTMA20/03/2023 15:44NOOA632701FCTMAFALSE 
632701TETDEPE15/03/2023 00:00NOOA632701TDEPEFALSE 
632701TETDEPA15/03/2023 00:00NOOA632701TDEPAFALSE 
632701TEGDELA15/03/2023 00:00NOOA632701GDELAFALSE 

 

What I've been trying to do, logically, is to get the latest non-exception event before the current event and the earliest non-exception event after it and after checking the difference between the current event's date and the events before and after, choose the closest one and define the current event's Responsible Party as the nearest event's event user code.

 

So far, I've been able to use the FILTER() function to get all the Non-Exception events onto a variable, but I'm unable to use the variable in other FILTER() functions (to get the previous and next events) since DAX doesn't allow me to reference the columns from the variable name.

 

Am I going about this with the wrong logic or is there a workaround to reference columns from the variable?

 

Thank you in advance for any help.

 

Need Help

2 Replies

  • danextian's avatar
    danextian
    Super User
    3 years ago

    Hi MuhammadP ,

     

    How do you know which event comes first? If we were to base in on Even Date, there are rows with the same timestamps

     

    • MuhammadP's avatar
      MuhammadP
      Frequent Visitor
      3 years ago

      Hi danextian,

       

      Thanks for your response.

       

      In my use case it doesn't affect the outcome much since it's usually the same user creating the event if the timestamps are the same. I just consider the first match.