Forum Discussion

mleech's avatar
mleech
Frequent Visitor
8 years ago
Solved

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 ...
Need Help
  • Anonymous's avatar
    Anonymous
    8 years ago

    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"
Anonymous's avatar
Anonymous
Not applicable
8 years ago

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"
mleech's avatar
mleech
Frequent Visitor
8 years ago

Thank you so much, this worked!!

  • ovetteabejuela's avatar
    ovetteabejuela
    Impactful Individual
    6 years ago

    is this recommended for large tables?

    • doubleclick's avatar
      doubleclick
      Resolver I
      4 years ago

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

      • AlexisOlson's avatar
        AlexisOlson
        Super User
        3 years ago

        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"