Forum Discussion

darianle's avatar
darianle
Regular Visitor
3 years ago
Solved

Find the Nearest Value One column to Another Based on ID

Good Morning,

 

I have been wrapping my head around this for days—numerous searches to no avail.

 

I have three columns:   ID, Enter, and Exit

For Each ID,  if there is an Enter Time, there should be a corresponding Exit Time (nearest Value).

Could anyone provide a Dax for this?

 

In my sample below, I have provided an output column as to what the expected results should be.

 

thanks!

 

 

Sample Date

https://www.dropbox.com/s/h6dnh4h17luz50l/Sample%20Data.xlsx?dl=0

Need Help
  • AlB's avatar
    AlB
    3 years ago

    Hi darianle 

    Try this calculated column:

    ClosestExit = 
VAR enter_ = Table1[Enter]
RETURN
    TOPN (
        1,
        CALCULATETABLE (
            DISTINCT ( Table1[Exit] ),
            ALLEXCEPT ( Table1, Table1[NumberID] ),
            Table1[Exit] >= enter_
        ),
        Table1[Exit] - enter_, ASC
    )

     

    		 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.

     

2 Replies

  • AlB's avatar
    AlB
    Icon for Community Champion rankCommunity Champion
    3 years ago

    Hi darianle 

    Try this calculated column:

    ClosestExit = 
VAR enter_ = Table1[Enter]
RETURN
    TOPN (
        1,
        CALCULATETABLE (
            DISTINCT ( Table1[Exit] ),
            ALLEXCEPT ( Table1, Table1[NumberID] ),
            Table1[Exit] >= enter_
        ),
        Table1[Exit] - enter_, ASC
    )

     

    		 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.

     

    • darianle's avatar
      darianle
      Regular Visitor
      3 years ago

      WoW! that worked great.. 

       

      The only issue now is: 

      "<ccon>There's not enough memory to complete this operation. Please try again later when there may be more memory available.</ccon>"

       

      But that's on me.

       

      Thanks again