Forum Discussion

xsaldana's avatar
xsaldana
Regular Visitor
4 years ago
Solved

Finding the different in a value when a condition from another column is met

Hello! I am having trouble finding the difference in unix time between readings at the same location. Below is an example of a table. The "Time in between readings" is the column I am attempting to create. I highlighted the table in terms of location to emphasize that the difference only needs to be taken if at the same location. 

 

 

 

This is the command I have, but it does not work properly. Any suggestions on how to do this?

Time in between readings = IF(ISBLANK(Data[temperature]) = FALSE() && Data[Location = Data[Location], VAR Diff = Data[unix time] - CALCULATE(SUM(Data[unix time]), Filter(Data, Data[Index] = EARLIER(Data[Index]) ) ) 

return 

IF(Diff = VALUE(Data[unix time]), 0, diff))

  • tamerj1's avatar
    tamerj1
    4 years ago

    xsaldana 
    Another solution with some help from power query (if applicaple) https://www.dropbox.com/t/Gj8t4ZxelU0zRmqA

    Time in between readings = 
VAR CurrentIndex = Data[Index]
VAR CurrentTime = Data[unix time]
VAR PreviousTime = 
    CALCULATE ( 
        VALUES ( Data[unix time] ),
        ALLEXCEPT ( Data, Data[Location] ),
        Data[Index] = CurrentIndex - 1
    )
RETURN
    IF (
        NOT ISBLANK ( Data[temperature] ) && NOT ISBLANK ( PreviousTime ),
        CurrentTime - PreviousTime
    )

     

9 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    4 years ago

    xsaldana , New column

     

    new column =
    var _max = max(filter(Table, Data[Location] = earlier(Data[Location]) && Data[unix time] < earlier( Data[Location] )),[unix time])
    return
    IF(not(ISBLANK(Data[temperature])) , Data[temperature] - max(filter(Table, Data[Location] = earlier(Data[Location]) && Data[unix time] =_max ), Data[temperature]) , blank())

    • xsaldana's avatar
      xsaldana
      Regular Visitor
      4 years ago

      amitchandak 

      Hello, thank you for taking the time to look at this! Unfortunately, I get an error when I use your command. It says "the expression refers to multiple columns. multiple columns cannot be converted to scalar value. 

  • tamerj1's avatar
    tamerj1
    Icon for Community Champion rankCommunity Champion
    4 years ago

    Hi xsaldana 

    you may try

     

    Time in between readings =
VAR CurrentTime = Data[unix time]
VAR LocationTable =
    CALCULATETABLE ( Data, ALLEXCEPT ( Data, Data[Location] ) )
VAR PreviousTime =
    MAXX (
        FILTER (
            CurrentTime,
            Data[unix time] < CurrentTime
                && Data[temperature] <> BLANK ()
        ),
        Data[unix time]
    )
RETURN
    IF ( NOT ( ISBLANK ( Data[temperature] ) ), CurrentTime - PreviousTime )

     

    • xsaldana's avatar
      xsaldana
      Regular Visitor
      4 years ago

      tamerj1 

      Thank you for suggesting this! Unfortunately, it did not work. It's stuck on "working on it"

      • tamerj1's avatar
        tamerj1
        Icon for Community Champion rankCommunity Champion
        4 years ago

        xsaldana 
        Apparantly this need to be optimized. However, and before going into further complicatios would you please try this

        Time in between readings =
VAR CurrentTime = Data[unix time]
VAR LocationTable =
    CALCULATETABLE ( Data, ALLEXCEPT ( Data, Data[Location] ) )
VAR T1 =
    FILTER ( CurrentTime, Data[unix time] < CurrentTime )
VAR T2 =
    FILTER ( T1, Data[temperature] <> BLANK () )
VAR PreviousTime =
    MAXX ( T2, Data[unix time] )
RETURN
    IF ( NOT ( ISBLANK ( Data[temperature] ) ), CurrentTime - PreviousTime )
  • tamerj1's avatar
    tamerj1
    Icon for Community Champion rankCommunity Champion
    4 years ago

    xsaldana 
    Another solution with some help from power query (if applicaple) https://www.dropbox.com/t/Gj8t4ZxelU0zRmqA

    Time in between readings = 
VAR CurrentIndex = Data[Index]
VAR CurrentTime = Data[unix time]
VAR PreviousTime = 
    CALCULATE ( 
        VALUES ( Data[unix time] ),
        ALLEXCEPT ( Data, Data[Location] ),
        Data[Index] = CurrentIndex - 1
    )
RETURN
    IF (
        NOT ISBLANK ( Data[temperature] ) && NOT ISBLANK ( PreviousTime ),
        CurrentTime - PreviousTime
    )