Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
filarap
Helper III
Helper III

Row Value based on criteria and previous value

Hi all,

 

I have a table with timestamps and unique ids. What i am trying to achieve is:

 

For each row, look at previous 30 minutes and return "Yes" if there was same Id. 

Is there a way to achieve something like this?

 

Table below shows an example (Not sure if table is 30 minutes, as i took random numbers)

 

Time StampIDResult
07:46:344No
09:08:381No
09:25:554No
09:37:2610No
10:04:482No
10:42:145No
11:08:102No
11:13:552Yes
11:38:248No
11:57:077No
12:17:1710No
12:28:4810Yes
12:50:249No
13:36:293No
14:15:2210No
14:21:073No
16:06:143No
16:20:388No
16:22:053Yes
16:40:4810No
17:00:585No
17:08:1010Yes
17:13:556Yes
17:29:466Yes

 

Thank you

Filarap

1 ACCEPTED SOLUTION
v-frfei-msft
Community Support
Community Support

Hi @filarap ,

 

To create two calculated column as below.

index = 
RANKX (
    FILTER ( 'Table', 'Table'[ID] = EARLIER ( 'Table'[ID] ) ),
    'Table'[Time Stamp],
    ,
    ASC,
    DENSE
)
Resu = 
VAR inx = 'Table'[index] - 1
VAR a =
    CALCULATE (
        MAX ( 'Table'[Time Stamp] ),
        FILTER (
            'Table',
            'Table'[index] = inx
                && 'Table'[ID] = EARLIER ( 'Table'[ID] )
        )
    )
VAR b = 'Table'[Time Stamp]
VAR diff =
    DATEDIFF ( a, b, MINUTE )
RETURN
    IF ( ISBLANK ( diff ) || diff > 30, "no", IF ( diff <= 30, "yes" ) )

Capture.PNG

 

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.

View solution in original post

4 REPLIES 4
Anonymous
Not applicable

Give a try using these instruction:

Table.Group(yourTab, {"ID"}, {{"Conteggio", each Table.RowCount(_), type number}, {"res", each List.Transform(delta(_[Time Stamp]),each Duration.TotalMinutes(_)<30 ))

 

"delta" is a function defined as follow:

let
diff= (Lista as list, optional def) =>
[shLista= {def}&List.RemoveLastN(Lista,1) ,
LT=List.Transform(List.Zip({Lista,shLista}), each _{0}-_{1})][LT]
in
diff

 

Anonymous
Not applicable


@Anonymous wrote:

Give a try using these instruction:

 

 Table.Group(yourTab, {"ID"}, {{"Conteggio", each Table.RowCount(_), type number}, {"res", each List.Transform(delta(_[Time Stamp]),each Duration.TotalMinutes(_)<30 ))

 

"delta" is a function defined as follow:

let
diff= (Lista as list, optional def) =>
[shLista= {def}&List.RemoveLastN(Lista,1) ,
LT=List.Transform(List.Zip({Lista,shLista}), each _{0}-_{1})][LT]
in
diff

 

 


Is, of course, important that the list is sorted, which is implicitly assumed in the previuos script.

Here a differente way to get the same result:

 

let
NotToOld= (Lista as list,Old as number, optional def) =>
let
LstAcc=List.Skip(List.Accumulate(List.Sort(Lista),[o=def,d={def}],(s,c)=> [o=c,d=s[d]&{Duration.TotalMinutes(c-s[o])<=Old}])[d])
in
LstAcc
in
NotToOld

 

 

PS

 I would like to attach a file, but I don't know if it's possible and how to do it

v-frfei-msft
Community Support
Community Support

Hi @filarap ,

 

To create two calculated column as below.

index = 
RANKX (
    FILTER ( 'Table', 'Table'[ID] = EARLIER ( 'Table'[ID] ) ),
    'Table'[Time Stamp],
    ,
    ASC,
    DENSE
)
Resu = 
VAR inx = 'Table'[index] - 1
VAR a =
    CALCULATE (
        MAX ( 'Table'[Time Stamp] ),
        FILTER (
            'Table',
            'Table'[index] = inx
                && 'Table'[ID] = EARLIER ( 'Table'[ID] )
        )
    )
VAR b = 'Table'[Time Stamp]
VAR diff =
    DATEDIFF ( a, b, MINUTE )
RETURN
    IF ( ISBLANK ( diff ) || diff > 30, "no", IF ( diff <= 30, "yes" ) )

Capture.PNG

 

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.

Thank you  

 

Realy great solution, work like a charm.

 

Regards

Filarap

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors