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
mleech
Frequent Visitor

Referencing a previous Row

 

Hi, 

 

I've been searching to find the answer with no luck.  What I am trying to do is reference a previous row using a DAX calculation.  I have a list of received call times and end call times by operator.  What I am trying to determind is which calls were recieved before the previous call was ended.  

 

For example from below operator 277BRANDON on May 7 recieved a call at 10:18:43 AM ended 11:02:49 AM the next call for 277BRANDON recieved at 10:50:31AM the same day.  I need to be able to flag the instances which this occurs

 

Call DateReceive TimeEnd TimeOperator
Friday, May 04, 20184:23:11 PM4:47:38 PM277BRANDON
Friday, May 04, 20184:47:19 PM5:38:12 PM277BRANDON
Friday, May 04, 20187:34:28 PM8:53:04 PM277BRANDON
Friday, May 04, 20189:03:45 PM9:33:09 PM277BRANDON
Monday, May 07, 20186:55:40 AM8:17:26 AM277BRANDON
Monday, May 07, 20188:11:48 AM9:04:02 AM277BRANDON
Monday, May 07, 20189:08:33 AM10:05:44 AM277BRANDON
Monday, May 07, 201810:18:43 AM11:02:49 AM277BRANDON
Monday, May 07, 201810:50:31 AM11:46:37 AM277BRANDON
Monday, May 07, 201811:39:39 AM12:17:21 PM277BRANDON
Monday, May 07, 201812:19:16 PM12:28:07 PM277BRANDON
Monday, May 07, 201812:46:18 PM1:30:32 PM277BRANDON
Monday, May 07, 20182:18:51 PM2:27:32 PM277BRANDON
Monday, May 07, 20182:24:59 PM2:38:50 PM277BRANDON
Monday, May 07, 20184:14:49 PM4:42:58 PM277BRANDON

 

Thanks,

  

 

1 ACCEPTED SOLUTION
Anonymous
Not applicable

You need to add an index column then use the following

 

Column =
VAR _EndTimeLookup =
    LOOKUPVALUE ( Table1[End Time], Table1[Index], Table1[Index] - 1 )
RETURN
    IF (
        NOT ( ISBLANK ( _EndTimeLookup ) ),
        IF ( Table1[Receive Time] < _EndTimeLookup, 1, 0 )
    )

This will make a column that returns 1 if the row is to be flagged and a 0 if not.

 

If you prefer you can also do it in Power Query, which is how I prefer to do items like this if I dont need it to be dynamic

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ldMxT8QwDAXgvxJ17mA7dpO87RBi64FYTzecxMICEtv9e9xLeywF4dHD9/yaJqfT8PT1/na5jmm+XBPpmIS4DuOgkAzm9DLfBi3ItQ9SysPr4fj4fBzO4x/eCbdOzDFYIt73eYV1ZYVlkEZ8A2WoddKQ3bddP39+/Phy9xPMoJQOfT8XyNSH/3knDK2deBkFScQ7qd66EyaQt9FIgBuu0C2BfT+0BROMkPmeoBNyCSUwsp/9upXldowc+Q2LaeCpG5+kgkowwXvzepO8kH/S/k38JUCWc7StNaSEvShsu3zLSzCKeAXr8ue2lyiwnZd4/gY=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Call Date" = _t, #"Receive Time" = _t, #"End Time" = _t, Operator = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Call Date", type date}, {"Receive Time", type time}, {"End Time", type time}, {"Operator", type text}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1),
    #"Added Custom" = Table.AddColumn(#"Added Index", "Last Row End", each try #"Added Index"{[Index]-1}[End Time] otherwise null,Time.Type),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Filter", each if [Last Row End] >= [Receive Time] and [Last Row End] <> null then 1 else 0,Int64.Type),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Last Row End"})
in
    #"Removed Columns"

View solution in original post

5 REPLIES 5
Anonymous
Not applicable

You need to add an index column then use the following

 

Column =
VAR _EndTimeLookup =
    LOOKUPVALUE ( Table1[End Time], Table1[Index], Table1[Index] - 1 )
RETURN
    IF (
        NOT ( ISBLANK ( _EndTimeLookup ) ),
        IF ( Table1[Receive Time] < _EndTimeLookup, 1, 0 )
    )

This will make a column that returns 1 if the row is to be flagged and a 0 if not.

 

If you prefer you can also do it in Power Query, which is how I prefer to do items like this if I dont need it to be dynamic

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ldMxT8QwDAXgvxJ17mA7dpO87RBi64FYTzecxMICEtv9e9xLeywF4dHD9/yaJqfT8PT1/na5jmm+XBPpmIS4DuOgkAzm9DLfBi3ItQ9SysPr4fj4fBzO4x/eCbdOzDFYIt73eYV1ZYVlkEZ8A2WoddKQ3bddP39+/Phy9xPMoJQOfT8XyNSH/3knDK2deBkFScQ7qd66EyaQt9FIgBuu0C2BfT+0BROMkPmeoBNyCSUwsp/9upXldowc+Q2LaeCpG5+kgkowwXvzepO8kH/S/k38JUCWc7StNaSEvShsu3zLSzCKeAXr8ue2lyiwnZd4/gY=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Call Date" = _t, #"Receive Time" = _t, #"End Time" = _t, Operator = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Call Date", type date}, {"Receive Time", type time}, {"End Time", type time}, {"Operator", type text}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1),
    #"Added Custom" = Table.AddColumn(#"Added Index", "Last Row End", each try #"Added Index"{[Index]-1}[End Time] otherwise null,Time.Type),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Filter", each if [Last Row End] >= [Receive Time] and [Last Row End] <> null then 1 else 0,Int64.Type),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Last Row End"})
in
    #"Removed Columns"

Thank you so much, this worked!!

is this recommended for large tables?

I have the same issue, it's taking absoutely ages to load

@ovetteabejuela@doubleclick This is definitely not the best way for large tables. Using a self-merge instead of a lookup should be better, though you might still run into memory issues to the table is too large.

 

For example,

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ldMxT8QwDAXgvxJ17mA7dpO87RBi64FYTzecxMICEtv9e9xLeywF4dHD9/yaJqfT8PT1/na5jmm+XBPpmIS4DuOgkAzm9DLfBi3ItQ9SysPr4fj4fBzO4x/eCbdOzDFYIt73eYV1ZYVlkEZ8A2WoddKQ3bddP39+/Phy9xPMoJQOfT8XyNSH/3knDK2deBkFScQ7qd66EyaQt9FIgBuu0C2BfT+0BROMkPmeoBNyCSUwsp/9upXldowc+Q2LaeCpG5+kgkowwXvzepO8kH/S/k38JUCWc7StNaSEvShsu3zLSzCKeAXr8ue2lyiwnZd4/gY=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Call Date" = _t, #"Receive Time" = _t, #"End Time" = _t, Operator = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Call Date", type date}, {"Receive Time", type time}, {"End Time", type time}, {"Operator", type text}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index0", 0, 1),
    #"Added Index1" = Table.Buffer(Table.AddIndexColumn(#"Added Index", "Index1", 1, 1, Int64.Type)),
    #"Merged Queries" = Table.NestedJoin(#"Added Index1", {"Index0"}, #"Added Index1", {"Index1"}, "Merge", JoinKind.LeftOuter),
    #"Expanded Added Index1" = Table.ExpandTableColumn(#"Merged Queries", "Merge", {"End Time"}, {"Last End Time"}),
    #"Added Custom" = Table.AddColumn(#"Expanded Added Index1", "Filter", each [Last End Time] <> null and [Last End Time] >= [Receive Time], type logical)
in
    #"Added Custom"

 

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.