Forum Discussion

thmonte's avatar
thmonte
Icon for Helper IV rankHelper IV
7 years ago

Count Rows between half of each other

I am trying to make a new column that gives each row a value of 1.  If the timestamp of each record are within a half hour of each other then I need to split that value .5 each.

 

So the calculation should look if

  • uID is the same AND
  • Location is the same AND
  • timestamp is within half hour of each other

then split the value

 

otherwise the row value is 1

 

Expected Result is the Value column

 

TimestampuIDLocationValue
1/1/18 3:00PMAZ1
1/1/18 9:00PMAZ1
1/1/19 4:00PMAZ1
1/1/18 3:00PMBX0.5
1/1/18 3:30PMBX0.5
1/1/18 4:00PMBZ1
1/1/18 3:00PMCY0.5
1/1/18 3:30PMCY0.5
Need Help

4 Replies

  • PattemManohar's avatar
    PattemManohar
    Icon for Community Champion rankCommunity Champion
    7 years ago

    thmonte Just an another way of achieving the same...

     

    Add a supporting column as below:

     

    Rnk = RANKX(FILTER(Test147SplitFlag,Test147SplitFlag[uID]=EARLIER(Test147SplitFlag[uID])),Test147SplitFlag[Timestamp],,ASC,Dense)

    Then add a new column as below (which will be the expected output)

     

    Result = 
VAR _CurrTime = Test147SplitFlag[Timestamp]
VAR _NextRowTime = LOOKUPVALUE(Test147SplitFlag[Timestamp],Test147SplitFlag[uID],Test147SplitFlag[uID],Test147SplitFlag[Location],Test147SplitFlag[Location],Test147SplitFlag[Rnk],Test147SplitFlag[Rnk]+1)
VAR _PrevRowTime = LOOKUPVALUE(Test147SplitFlag[Timestamp],Test147SplitFlag[uID],Test147SplitFlag[uID],Test147SplitFlag[Location],Test147SplitFlag[Location],Test147SplitFlag[Rnk],Test147SplitFlag[Rnk]-1)
RETURN IF(DATEDIFF(_CurrTime,_NextRowTime,MINUTE)=30 || DATEDIFF(_PrevRowTime,_CurrTime,MINUTE)=30,0.5,1)

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      I took a stab at this one as well :smileyhappy:

       

      Used Power Query to get index grouped by ID and Location.

      Final table from PQ:

       

      Using that index, created a calculated column to find the time between:

      TimeBetween = 
VAR CurrentID = 'CountRows Between Use'[uID]
VAR CurrentLocation = 'CountRows Between Use'[Location]
VAR CurrentIndex = 'CountRows Between Use'[Index]
VAR CurrentTime = 'CountRows Between Use'[Timestamp]
Return

VAR PrevTime=
CALCULATE(
   MAX ( 'CountRows Between Use'[Timestamp]),
        FILTER ( 
            ALL( 'CountRows Between Use'),
            CurrentID = 'CountRows Between Use'[uID]
            && CurrentLocation = 'CountRows Between Use'[Location]
            && CurrentIndex = 'CountRows Between Use'[Index] +1
            && CurrentTime >=  'CountRows Between Use'[Timestamp]
        )
    )
RETURN

DATEDIFF(PrevTime,'CountRows Between Use'[Timestamp],MINUTE)

      Then a simple column to give a 1 if the above column was blank or above 30, and .5 if  below 30

      InterValue = 
IF( ISBLANK('CountRows Between Use'[TimeBetween]),1,
	IF( 'CountRows Between Use'[TimeBetween] > 30,
		1, 
		.5 
	)
)

       Then a final column where it looks at the next row and if that was .5 then make the current row .5, or if the current row is .5.  If not either of those, give 1:

      Expected Output = 
VAR CurrentID = 'CountRows Between Use'[uID]
VAR CurrentLocation = 'CountRows Between Use'[Location]
VAR CurrentIndex = 'CountRows Between Use'[Index]
VAR CurrentTime = 'CountRows Between Use'[Timestamp]
Return

VAR PrevValue=
CALCULATE(
    MAX( 'CountRows Between Use'[InterValue]),
        FILTER ( 
             All('CountRows Between Use'),
            CurrentID = 'CountRows Between Use'[uID]
            && CurrentLocation = 'CountRows Between Use'[Location]
            && CurrentIndex = 'CountRows Between Use'[Index] -1
        )
    )
RETURN

IF( OR(
        PrevValue=.5,
        'CountRows Between Use'[InterValue] =.5),
        .5,1
)

      Final table.  Things could probably be combined, but the gist is there:

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    7 years ago

    See if this works, could probably be combined into a single column:

     

    Column = 
VAR __earlier = MAXX(FILTER(ALL('Table17'),[uID]=EARLIER([uID])&&[Location]=EARLIER([Location])&&[Timestamp]<EARLIER([Timestamp])),[Timestamp])
VAR __later = MINX(FILTER(ALL('Table17'),[uID]=EARLIER([uID])&&[Location]=EARLIER([Location])&&[Timestamp]>EARLIER([Timestamp])),[Timestamp])
VAR __earlierDiff = DATEDIFF(__earlier,[Timestamp],MINUTE)
VAR __laterDiff = DATEDIFF([Timestamp],__later,MINUTE)
RETURN __laterDiff


Column 1 = 
VAR __earlier = MAXX(FILTER(ALL('Table17'),[uID]=EARLIER([uID])&&[Location]=EARLIER([Location])&&[Timestamp]<EARLIER([Timestamp])),[Timestamp])
VAR __later = MINX(FILTER(ALL('Table17'),[uID]=EARLIER([uID])&&[Location]=EARLIER([Location])&&[Timestamp]>EARLIER([Timestamp])),[Timestamp])
VAR __earlierDiff = DATEDIFF(__earlier,[Timestamp],MINUTE)
VAR __laterDiff = DATEDIFF([Timestamp],__later,MINUTE)
RETURN __earlierDiff


Column 2 = IF((NOT(ISBLANK([Column])) && [Column] <= 30) || (NOT(ISBLANK([Column 1])) && [Column 1] <= 30),.5,1)

    See attached Table17.

     

    • thmonte's avatar
      thmonte
      Icon for Helper IV rankHelper IV
      7 years ago

      One downfall with this and maybe I should have been more specific in the requirements but its hard coded to .5. What if 3 records are foundi n hte query?  That value should be .33 in all 3 rows.  It should take the amount of rows that fits the 30 minutes window and divide by 1 to get an even split.